left jion 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 型号,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)*/
[解决办法]
- SQL code
--对待这种问题，我建议先聚合然后再JOINWITH B AS( SELECT typename,SUM(num) AS num FROM tblb GROUP BY typename),C AS( SELECT typename,SUM(num) AS num FROM tblc GROUP BY typename)SELECT A.typename, ISNULL(B.num,0) AS 共领料, ISNULL(C.num,0) AS 共入库 FROM tbla AS ALEFT JOIN B ON A.typename = B.typenameLEFT JOIN C ON A.typename = C.typename typename 共领料 共入库-------------------------------------------------- ----------- -----------dt-051 40 50dt-052 30 0dt-053 20 10dt-054 0 0dt-055 0 50dt-056 50 50(6 row(s) affected)
[解决办法]
要知道left join 的执行是先求笛卡尔积 然后再用WHERE条件过滤
你这里left join 了两次。造成了一对多的多次关联，SO。。
[解决办法]
要警惕多次left join可能带来的风险，看下面的结果你就明白了
- SQL code
select *from tbla as a left join tblb as b on b.typename = a.typename left join tblc as c on c.typename = a.typename /*typename typename num typename num-------------------------------------------------- -------------------------------------------------- ----------- -------------------------------------------------- -----------dt-051 dt-051 10 dt-051 50dt-051 dt-051 30 dt-051 50dt-052 dt-052 30 NULL NULLdt-053 dt-053 20 dt-053 10dt-054 NULL NULL NULL NULLdt-055 NULL NULL dt-055 20dt-055 NULL NULL dt-055 30dt-056 dt-056 30 dt-056 30dt-056 dt-056 30 dt-056 20dt-056 dt-056 20 dt-056 30dt-056 dt-056 20 dt-056 20(11 row(s) affected)*/
[解决办法]
第一种SQL语句是先联合再汇总，而第二种SQL语句是先汇总再联合。
以dt-051为例，tbla联合tblb时是两条条记录，如下所示：
dt-05110
dt-05130
dt-05230
dt-05320
dt-054NULL
dt-055NULL
dt-05630
dt-05620
再联合tblc时，还是两条记录，如下所示，这样汇总tblc的[num]列就会翻倍。
dt-0511050
dt-0513050
dt-05230NULL
dt-0532010
dt-054NULLNULL
dt-055NULL20
dt-055NULL30
dt-0563030
dt-0563020
dt-0562030
dt-0562020。
而第二种语句汇总后再联合就不会有这种问题。
[解决办法]
楼上的已经很详细了，希望楼主顺利解决问题。
[解决办法]
简单的是说就是它 join b 的结果再去和c join
最近sql 板块有活跃起来了哈。