先distinct,再sun(),SQL语句怎么写?
我要做一个有关进销存的统计:WareTbl是商品表(PLU和Price1是这个表里面的),DepotInTbl是入库方面的表
"select PLUNo,Name,sum(Qty) as dQuality,Price1 from DepotInTbl,WareTbl where PLUNo=PLU and Qty>0"
这条查询是以前的,现在需要修改:由于WareTbl涉及到多台收款机的数据,所以里面可能有编号(PLU)一样的商品,现在的问题是:
我想先把WareTbl中的重复记录先过滤掉,然后再联合DepotInTbl一起查询,计算sum(Qty),我改了SQL语句:
"select distinct(PLUNo),Name,sum(Qty) as dQuality,Price1 from DepotInTbl,WareTbl where PLUNo=PLU and Qty>0"
可是实际情况是:
WareTbl中有几条重复记录,sum(Qty)就多算了多少~~~~~
求助,我应该怎么写这个SQL语句?sum(distinct Qty)不行!
[解决办法]
不需要distinct
用分组就可以了
select PLUNo,sum(Qty) from WareTbl group by PLUNo
[解决办法]
select PLUNo,Name,sum(Qty) as dQuality,Price1 from
(
select distinct PLUNo,Name,Qty ,Price1 from DepotInTbl,WareTbl where PLUNo=PLU and Qty>0
)