如下表 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汇总计算
------解决方案--------------------
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
------解决方案--------------------
agree
thanks