sum left join 多次引发的问题

--测试数据如下
create table tbla
(
    typename varchar(50)
)
insert into tbla
select 'dt-051' union all
select 'dt-052' union all
select 'dt-053' union all
select 'dt-054' union all
select 'dt-055' union all
select 'dt-056'


create table tblb
(
    typename varchar(50),
    num int,
)
insert into tblb
select 'dt-056',30 union all
select 'dt-056',20 union all
select 'dt-051',10 union all
select 'dt-052',30 union all
select 'dt-053',20 union all
select 'dt-051',30


create table tblc
(
    typename varchar(50),
    num int,
)
insert into tblc
select 'dt-056',30 union all
select 'dt-055',20 union all
select 'dt-056',20 union all
select 'dt-055',30 union all
select 'dt-051',50 union all
select 'dt-053',10

select 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.typename
order by a.typename asc
--结果没有问题
/*
dt-051    40
dt-052    30
dt-053    20
dt-054    0
dt-055    0
dt-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.typename
order by a.typename asc

--结果没有问题
/*
dt-051    50
dt-052    0
dt-053    10
dt-054    0
dt-055    50
dt-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.typename
order by a.typename asc

--结果就出现问题了
/*
dt-051    40    100
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    50
dt-056    100    100
*/

--正常结果如下
/*
dt-051    40    50
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    50
dt-056    50    50
*/



--使用如下的sql语句 结果正确 
select a.typename,isnull(b.sumnum,0) as 共领料,isnull(c.sumnum,0) as 共入库
from tbla as a
left join (select typename,sum(num) as sumnum from tblb group by typename)
b on b.typename = a.typename
left join (select typename,sum(num) as sumnum from tblc  group by typename)
c on c.typename = a.typename
order by a.typename asc
--结果
/*
dt-051    40    50
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    50
dt-056    50    50
*/


--多次联合查询会造成如下问题
select 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 = a.typename


/*
dt-051    10    50
dt-051    30    50
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    20
dt-055    0    30
dt-056    30    30
dt-056    30    20
dt-056    20    30
dt-056    20    20
*/


--提供另外的写法

select a.typename as 型号,
       isnull(bb.outqty,0) as'共领料',
       isnull(cc.inqty,0) as'共入库'from tbla as a 
outer apply(selectsum(num) outqty from tblb as b 
            where b.typename=a.typename) bb 
outer apply(selectsum(num) inqty from tblc as c 
            where c.typename=a.typename) cc



--在提供一个写法

WITH B AS
(
    SELECT typename,SUM(num) AS num FROM tblb GROUPBY typename
),
C AS
(
    SELECT typename,SUM(num) AS num FROM tblc GROUPBY typename
)
SELECT 
    A.typename,
    ISNULL(B.num,0)  AS 共领料,
    ISNULL(C.num,0) AS 共入库 
FROM tbla AS A
LEFTJOIN B
    ON A.typename = B.typename
LEFTJOIN C
    ON A.typename = C.typename

 

转载于:https://www.cnblogs.com/acyy/archive/2012/08/16/2642749.html