SQL外部联接返回太多

Question

我有两个表：

create table custinvoicetrans
      ( LINEAMOUNTMST numeric(28,12)
      , INVENTTRANSID nvarchar(20)
      , DATAAREAID nvarchar (4)
      )
;
insert into custinvoicetrans (LINEAMOUNTMST, INVENTTRANSID, DATAAREAID) VALUES
(2685.850000000000, N'00006840_069', N'dk-l'), 
(6162.220000000000, N'00034511_069', N'DK-L'), 
(-1453.350000000000, N'00035042_069', N'DK-L')
;
create table INVENTTRANS
      ( CUSTVENDAC nvarchar(20)
      , ItemId nvarchar(20)
      , QTY numeric(28,12)
      , COSTAMOUNTPOSTED numeric(28,12)
      , COSTAMOUNTPHYSICAL numeric(28,12)
      , COSTAMOUNTADJUSTMENT numeric(28,12)
      , DATAAREAID nvarchar(20)
      , INVENTTRANSID nvarchar(20)
      )
;
insert into INVENTTRANS (CUSTVENDAC, ItemId, QTY, COSTAMOUNTPOSTED, COSTAMOUNTPHYSICAL, COSTAMOUNTADJUSTMENT, DATAAREAID, INVENTTRANSID) VALUES
(N'100251', N'F20100003', -120.000000000000, 0.000000000000, 0.000000000000, -1034.630000000000, N'dk-l', N'00006840_069'), 
(N'100251', N'F20301000', -50.000000000000, -664.500000000000, -664.500000000000, 0.000000000000, N'DK-L', N'00034511_069'), 
(N'100251', N'F20301000', -162.000000000000, -2152.980000000000, -2152.980000000000, 0.000000000000, N'DK-L', N'00034511_069'), 
(N'100251', N'F20301000', 50.000000000000, 664.500000000000, 664.500000000000, 0.000000000000, N'DK-L', N'00035042_069')
;

当我加入下面这样的表时，当从cusinvoicetrans获取值时，我会得到项目"20301000"的双值。我不知道该怎么办——我花了好几个小时用各种不同的方式来解决这个问题，但我无法得到合适的数量。

请帮帮我。

我知道出了什么问题，但我找不到解决办法。

Martin

    select CUSTVENDAC
      , ITEMID
      , sum(QTY) QTY
      , sum(LINEAMOUNTMST) Revenue
      , sum(LINEAMOUNTMST) + sum(COSTAMOUNTADJUSTMENT) + sum(COSTAMOUNTPHYSICAL) Margin
from INVENTTRANS
left outer join CUSTINVOICETRANS on INVENTTRANS.INVENTTRANSID = CUSTINVOICETRANS.INVENTTRANSID
                                    and INVENTTRANS.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
where INVENTTRANS.DATAAREAID = 'dk-l'
      and INVENTTRANS.CUSTVENDAC = '100251'
group by INVENTTRANS.CUSTVENDAC
      , INVENTTRANS.ITEMID
order by INVENTTRANS.CUSTVENDAC
      , INVENTTRANS.ITEMID

Answer 1

您可以为INVENTRANS表创建一个子查询

 select CUSTVENDAC
          , ITEMID
          , sum(QTY) QTY
          , sum(LINEAMOUNTMST) Revenue
          , sum(LINEAMOUNTMST) + sum(COSTAMOUNTADJUSTMENT) + sum(COSTAMOUNTPHYSICAL) Margin
    from (
        Select 
        CUSTVENDAC,
        ITEMID, 
        sum(QTY) 'QTY',
        sum(COSTAMOUNTPOSTED) 'COSTAMOUNTPOSTED',
        sum(COSTAMOUNTPHYSICAL) 'COSTAMOUNTPHYSICAL',
        sum(COSTAMOUNTADJUSTMENT) 'COSTAMOUNTADJUSTMENT',
        DATAAREAID,
        INVENTTRANSID
    from INVENTTRANS
    group by 
        CUSTVENDAC,
        ITEMID, 
        DATAAREAID,
        INVENTTRANSID
    )INVENTTRANS
    left outer join #CUSTINVOICETRANS CUSTINVOICETRANS on INVENTTRANS.INVENTTRANSID =          CUSTINVOICETRANS.INVENTTRANSID
                                    and INVENTTRANS.DATAAREAID =     CUSTINVOICETRANS.DATAAREAID
where INVENTTRANS.DATAAREAID = 'dk-l'
      and INVENTTRANS.CUSTVENDAC = '100251'
group by INVENTTRANS.CUSTVENDAC
      , INVENTTRANS.ITEMID
order by INVENTTRANS.CUSTVENDAC
      , INVENTTRANS.ITEMID

Answer 2

您应该注意在On.中使用谓词

"非常重要的是要理解，对于外部连接，ON和WHERE子句扮演着非常不同的角色，因此，它们不是可交换的WHERE子句仍然执行简单的筛选角色——即保留真实案例，丢弃虚假和未知案例。使用类似的内容，并在where子句中使用谓词。然而ON子句并没有起到简单的过滤作用；相反，它更匹配的角色。换句话说，保留一侧的一行返回ON谓词是否为其找到匹配项。所以ON谓词仅确定非保留端的哪些行与保留端的行匹配--而不是是否返回行从保存下来的一侧。"**考试70-461:查询Microsoft SQL Server2012年