- SQL code
--测试数据如下create table tbla( typename varchar(50))insert into tblaselect 'dt-051' union allselect 'dt-052' union allselect 'dt-053' union allselect 'dt-054' union allselect 'dt-055' union allselect 'dt-056'create table tblb( typename varchar(50), num int,)insert into tblbselect 'dt-056',30 union allselect 'dt-056',20 union allselect 'dt-051',10 union allselect 'dt-052',30 union allselect 'dt-053',20 union allselect 'dt-051',30create table tblc( typename varchar(50), num int,)insert into tblcselect 'dt-056',30 union allselect 'dt-055',20 union allselect 'dt-056',20 union allselect 'dt-055',30 union allselect 'dt-051',50 union allselect 'dt-053',10select a.typename as 型号,sum(isnull(b.num,0)) as 共领料 from tbla as a left join tblb as b on b.typename = a.typename group by a.typenameorder by a.typename asc--结果没有问题/*dt-051 40dt-052 30dt-053 20dt-054 0dt-055 0dt-056 50*/select a.typename as 型号,sum(isnull(c.num,0)) as 共入库 from tbla as a left join tblc as c on c.typename = a.typename group by a.typenameorder by a.typename asc--结果没有问题/*dt-051 50dt-052 0dt-053 10dt-054 0dt-055 50dt-056 50*/--但是select a.typename as 型号,sum(isnull(b.num,0)) as 共领料,sum(isnull(c.num,0)) as 共入库 from tbla as a left join tblb as b on b.typename = a.typename left join tblc as c on c.typename = a.typename group by a.typename,b.typename,c.typenameorder by a.typename asc--结果就出现问题了/*dt-051 40 100dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 50dt-056 100 100*/--正常结果如下/*dt-051 40 50dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 50dt-056 50 50*/--问题出在哪--使用如下的sql语句 结果就是正确的 区别在哪求解释select a.typename,isnull(b.sumnum,0) as 共领料,isnull(c.sumnum,0) as 共入库from tbla as aleft join (select typename,sum(num) as sumnum from tblb group by typename)b on b.typename = a.typenameleft join (select typename,sum(num) as sumnum from tblc group by typename)c on c.typename = a.typenameorder by a.typename asc--结果/*dt-051 40 50dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 50dt-056 50 50*/
------解决方案--------------------
因为有2重一对多的关联,影响了最终的记录笔数.
以dt-051为例,
第一次left join tblb,是1笔(tbla的)*2笔(tblb的)得到2笔,
第二此left join tblc,是2笔(已关联得到的)*1笔(tblc的),
最终得到2笔入库num=50的,故错误结果里dt-051的入库=100.
------解决方案--------------------
- SQL code
--看如下sqlselect a.typename as 型号,isnull(b.num,0) as 共领料,isnull(c.num,0) as 共入库 from tbla as a left join tblb as b on b.typename = a.typename left join tblc as c on c.typename = b.typename --结果集如下型号 共领料 共入库-------------------------------------------------- ----------- -----------dt-051 10 50dt-051 30 50dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 0dt-056 30 30dt-056 30 20dt-056 20 30dt-056 20 20(10 row(s) affected)--此时你在用sum,那么它的操作时累加的所以会出现这个问题
------解决方案--------------------
提供另一种写法,
- SQL code
create table tbla( typename varchar(50))insert into tblaselect 'dt-051' union allselect 'dt-052' union allselect 'dt-053' union allselect 'dt-054' union allselect 'dt-055' union allselect 'dt-056'create table tblb( typename varchar(50), num int)insert into tblbselect 'dt-056',30 union allselect 'dt-056',20 union allselect 'dt-051',10 union allselect 'dt-052',30 union allselect 'dt-053',20 union allselect 'dt-051',30create table tblc( typename varchar(50), num int)insert into tblcselect 'dt-056',30 union allselect 'dt-055',20 union allselect 'dt-056',20 union allselect 'dt-055',30 union allselect 'dt-051',50 union allselect 'dt-053',10select a.typename as 型号, isnull(bb.outqty,0) as '共领料', isnull(cc.inqty,0) as '共入库'from tbla as a outer apply(select sum(num) outqty from tblb as b where b.typename=a.typename) bb outer apply(select sum(num) inqty from tblc as c where c.typename=a.typename) cc/*型号 共领料 共入库-------------------------------------------------- ----------- -----------dt-051 40 50dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 50dt-056 50 50(6 row(s) affected)*/