剔除重复记录

Question

如下表 DNDetail
DNNumber   CompanyCode  PlantCode  ShipQty  PickQty  PackQty  Location
0800017834 1600 1601    4.0000   4.0000   0.0000 1001
0800017837 1600 1601    1.0000   1.0000   1.0000 1011
0800017837 1600 1601    1.0000   1.0000   0.0000 1011
0800017837 1600 1601    1.0000   1.0000   0.0000 1011
0800017838 1600 1601    1.0000   1.0000   0.0000 1011
0800017838 1600 1601    1.0000   1.0000   0.0000 1011
0800017838 1600 1601    1.0000   1.0000   0.0000 1011
0800017842 1600 1601    1.0000   1.0000   0.0000 1011
0800017844 1600 1601    1.0000   1.0000   0.0000 1003
0800017845 1600 1601    1.0000   1.0000   0.0000 1011
0800017846 1600 1601    1.0000   1.0000   0.0000 1003
0800017847 1600 1601    1.0000   1.0000   0.0000 1001
0800017847 1600 1601    1.0000   1.0000   0.0000 1003最终希望得到的结果是:
DNNumber   CompanyCode  PlantCode  ShipQty  PickQty  PackQty
0800017847 1600 1601    2.0000   2.0000   0.0000
DNNumber,CompanyCode,PlantCode作为查询条件,
Location为空，或者DNNumber有多条不同Location记录,就取出来对ShipQty, PickQty, PackQty汇总计算

Answer 1

select DNNumber ,  CompanyCode , PlantCode , sum(ShipQty),sum(PickQty),sum(PackQty) 
 from  table_name ,
group by  DNNumber ,  CompanyCode , PlantCode 

Answer 2

select 
     DNNumber ,  CompanyCode , PlantCode , 
     sum(ShipQty),sum(PickQty),sum(PackQty)  
from  table_name group by  DNNumber ,  CompanyCode , PlantCode 

Answer 3

--原始数据：@DNDetail
declare @DNDetail table(DNNumber varchar(10),CompanyCode int,PlantCode int,ShipQty decimal(5,4),PickQty decimal(5,4),PackQty decimal(5,4),Location int)
insert @DNDetail
select '0800017834',1600,1601,4.0000,4.0000,0.0000,1001 union all
select '0800017837',1600,1601,1.0000,1.0000,1.0000,1011 union all
select '0800017837',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017837',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017838',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017838',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017838',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017842',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017844',1600,1601,1.0000,1.0000,0.0000,1003 union all
select '0800017845',1600,1601,1.0000,1.0000,0.0000,1011 union all
select '0800017846',1600,1601,1.0000,1.0000,0.0000,1003 union all
select '0800017847',1600,1601,1.0000,1.0000,0.0000,1001 union all
select '0800017847',1600,1601,1.0000,1.0000,0.0000,1003select
DNNumber,
CompanyCode,
PlantCode,
ShipQty=sum(ShipQty),
PickQty=sum(PickQty),
PackQty=sum(PackQty)
from @DNDetail
where DNNumber is null or DNNumber in (select DNNumber from @DNDetail group by DNNumber having(count(distinct Location))>1)
group by DNNumber,CompanyCode,PlantCode
/*
DNNumber   CompanyCode PlantCode   ShipQty   PickQty     PackQty
---------- ----------- ----------- --------- ----------- --------
0800017847 1600        1601        2.0000    2.0000      .0000
*/

Answer 4

DNNumber,CompanyCode,PlantCode作为查询条件, 
Location为空，或者DNNumber有多条不同Location记录,就取出来对ShipQty, PickQty, PackQty汇总计算
楼上正解,但是最后的查询语句该是
select
        DNNumber,
        CompanyCode,
        PlantCode,
        ShipQty=sum(ShipQty),
        PickQty=sum(PickQty),
        PackQty=sum(PackQty)
from @DNDetail
where Location is null or DNNumber in (select DNNumber from @DNDetail group by DNNumber having(count(distinct Location))>1)
group by DNNumber,CompanyCode,PlantCode