[求解决]2表数据有有关问题

[求解决]2表数据有问题
表1:入库资料,字段:入库日期,入库金额,入库单位编号,入库单位名称....
表2:入库付款资料,字段:所属入库年份,所属入库月份,入库单位编号,已付款金额....

查询想到2表合在一起的数据:

如下是我写的代码:

SELECT a.rk_dwid,max(a.rk_dw) as dwmc,year(rk_date) as rknf,SUM(a.rk_spzj) as fkje,SUM(b.yfl_fkje) as yfje FROM rkd a
left outer join ysyfjl b on b.yfl_type='gys' and b.yfl_mark='付款' and a.rk_dwid=b.gg_id and YEAR(a.rk_date)=convert(int,LEFT(b.yfl_nf,4))
where a.rk_style='正常入库' and a.rk_fbbj='000' group by a.rk_dwid,year(a.rk_date) order by dwmc,rknf

注解:rk_dwid=入库单位编号
rk_dw=入库单位
rk_style=入库资料中正常入库的记录标记
rk_fbbj=入库所属部门
yfl_type=付款类型(gys:供应商)
yfl_mark=付款记录标记
yfl_fkje=已付款金额
yfl_nf=入库年份

问题:
里面的SUM(b.yfl_fkje) as yfje 生成的数值是错误的值!求大家帮我看看是哪里出来问题

[解决办法]

SQL code

SELECT  a.rk_dwid ,        MAX(a.rk_dw) AS dwmc ,        YEAR(rk_date) AS rknf ,        SUM(a.rk_spzj) AS fkje ,        SUM(ISNULL(b.yfl_fkje,0)) AS yfjeFROM    rkd a        LEFT OUTER JOIN ysyfjl b ON b.yfl_type = 'gys'                                    AND b.yfl_mark = '付款'                                    AND a.rk_dwid = b.gg_id                                    AND YEAR(a.rk_date) = CONVERT(INT, LEFT(b.yfl_nf,4))WHERE   a.rk_style = '正常入库'  AND a.rk_fbbj = '000'GROUP BY a.rk_dwid , YEAR(a.rk_date)ORDER BY dwmc , rknf
[解决办法]
这样直接看怎么能看出来？
[解决办法]
探讨
SQL code

SELECT  a.rk_dwid ,
        MAX(a.rk_dw) AS dwmc ,
        YEAR(rk_date) AS rknf ,
        SUM(a.rk_spzj) AS fkje ,
        SUM(ISNULL(b.yfl_fkje,0)) AS yfje
FROM    rkd a
        LE……

[解决办法]
猜测：

主表和子表放一起做聚合，如果子表记录有多行，对主表的聚合是否会出现重复统计。
[解决办法]
try:
SQL codeSELECT a.rk_dwid,a.rk_dw as dwmc,year(a.rk_date) as 入库年份,SUM(a.rk_spzj) as fkje,SUM(b.yfl_fkje) as yfje,SUM(a.rk_spzj)-SUM(b.yfl_fkje) as 未付金额FROM rkd a left join ysyfjl b on a.rk_dwid=b.gg_id and YEAR(a.rk_date)=convert(int,LEFT(b.yfl_nf,4)) and b.yfl_type='gys' and b.yfl_mark='付款'where a.rk_style='正常入库' and a.rk_fbbj='000'group by a.rk_dwid,a.rk_dw,year(a.rk_date)

[求解决]2表数据有有关问题

热点推荐