select prodpart.productid from prodpart,parts where prodpart.partid=parts.partid and parts.partid in (select partid from parts where mouldid=(select mouldid from parts where partid='1'))得到的结果正确的,是 productid 1 1 1 2 就是SUM时计算的不正确
应该为mouldid是“1”的相关的产品应该如下: productid 1 1 1 2select sum(sales.saleqty) as 'sum' from sales s inner join (select productid,partid from prodpart where partid in (select partid from parts where mouldid='1' )) p on s.productid=p.productid group by s.productid
你的SALES表中,PARTID=1的纪录是不是只有一条,你这样用IN只能查出一条,要用关联才可以得到你的效果 select sum(A.saleqty) from sales A INNER JOIN (select prodpart.productid from prodpart INNER JOIN parts ON prodpart.partid=parts.partid AND parts.mouldid=(select mouldid from parts where partid='1')) B ON A.productid=B.productid WHERE A.saleyear=2001 and A.salemonth=1
where prodpart.partid=parts.partid and parts.partid in
(select partid from parts
where mouldid=(select mouldid from parts
where partid='1'))得到的结果正确的,是
productid
1
1
1
2
就是SUM时计算的不正确
productid
1
1
1
2select sum(sales.saleqty) as 'sum' from sales s inner join
(select productid,partid from prodpart
where partid in (select partid from parts where mouldid='1' )) p
on s.productid=p.productid
group by s.productid
select sum(A.saleqty)
from sales A INNER JOIN
(select prodpart.productid
from prodpart INNER JOIN parts
ON prodpart.partid=parts.partid AND parts.mouldid=(select mouldid from parts where partid='1')) B
ON A.productid=B.productid
WHERE A.saleyear=2001 and A.salemonth=1