sqlstr = "SELECT c.名称,c.ID1, SUM(a.数量) AS 出库数量,SUM(b.数量) AS 退料数量 FROM 产品 c"
sqlstr = sqlstr & " LEFT OUTER JOIN 出库单明细 a ON c.ID1 = a.产品ID "
sqlstr = sqlstr & " LEFT OUTER JOIN 退料单明细 b ON c.ID1 = b.产品ID where "
sqlstr = sqlstr & "(c.类别ID like'%01%') "
sqlstr = sqlstr & " GROUP BY c.ID1,c.名称"
我想把出库单及退料单中的物品消耗数量汇总,但现在的问题是:如果退料单中数量为0是对的,便如果出库明细单中有几项,而退料数量会循环几次。所以退料单汇总出错。怎样改此语句呢?
sqlstr = sqlstr & " LEFT OUTER JOIN 出库单明细 a ON c.ID1 = a.产品ID "
sqlstr = sqlstr & " LEFT OUTER JOIN 退料单明细 b ON c.ID1 = b.产品ID where "
sqlstr = sqlstr & "(c.类别ID like'%01%') "
sqlstr = sqlstr & " GROUP BY c.ID1,c.名称"
我想把出库单及退料单中的物品消耗数量汇总,但现在的问题是:如果退料单中数量为0是对的,便如果出库明细单中有几项,而退料数量会循环几次。所以退料单汇总出错。怎样改此语句呢?
from (
select c.ID1,c.名称,SUM(isnull(a.数量,0)) AS 出库数量
from 出库单明细 a
left outer join 产品 c on a.产品ID=c.ID1
where c.类别ID like '%01%'
group by c.ID1,c.名称) x
left outer join (
select c.ID1,c.名称,SUM(isnull(b.数量,0)) AS 退料数量
from 退料单明细 b
left outer join 产品 c on b.产品ID=c.ID1
where c.类别ID like '%01%'
group by c.ID1,c.名称) y
on x.ID1=y.ID1 and x.名称=y.名称分开写
sqlstr = "SELECT c.名称,c.ID1, SUM(a.数量) AS 出库数量,SUM(b.数量) AS 退料数量 FROM 产品 c"
sqlstr = sqlstr & " LEFT OUTER JOIN (select 产品ID,sum(数量)数量 from 出库单明细 group by 产品ID) a ON c.ID1 = a.产品ID "
sqlstr = sqlstr & " LEFT OUTER JOIN (select 产品ID,sum(数量)数量 from 退料单明细 group by 产品ID) b ON c.ID1 = b.产品ID where "
sqlstr = sqlstr & "(c.类别ID like'%01%') "
sqlstr = sqlstr & " GROUP BY c.ID1,c.名称" 看看合不合用