sql server 2005错误输出(sql server 2005 wrong output)
我有3个表库存,向内,问题
股票表的列和数据:
part_no | part_name | totalqty 10100 ciol 30 112233 abc 20 123456 coper 50向内表:
part_no | qty 123456 10 123456 20 10100 20 112233 15 10100 25问题表:
part_no | qty 112233 20 112233 15 123456 10 112233 25 10100 40 10100 20我想要的输出:
part_no | part_name |inwardQty |issueQty 10100 coil 45 60 112233 abc 15 60 123456 coper 30 10以下是我写的查询,但没有给出我想要的输出
select s.part_no,s.part_name,sum(i.qty) as inwardQty,sum(p.qty)as issueQty from stock s left join inward i on s.part_no = i.part_no left join issue p on s.part_no = p.part_no group by s.part_no,s.part_name通过此查询获取以下输出:
part_no | part_name |inwardQty |issueQty 10100 coil 90 120 112233 abc 45 60 123456 coper 30 20I have 3 table stock,inward,issue
Stock table's columns and data :
part_no | part_name | totalqty 10100 ciol 30 112233 abc 20 123456 coper 50inward table :
part_no | qty 123456 10 123456 20 10100 20 112233 15 10100 25issue table :
part_no | qty 112233 20 112233 15 123456 10 112233 25 10100 40 10100 20my desired output :
part_no | part_name |inwardQty |issueQty 10100 coil 45 60 112233 abc 15 60 123456 coper 30 10following is the query i have written,but not giving my desired output
select s.part_no,s.part_name,sum(i.qty) as inwardQty,sum(p.qty)as issueQty from stock s left join inward i on s.part_no = i.part_no left join issue p on s.part_no = p.part_no group by s.part_no,s.part_namegetting following output by this query :
part_no | part_name |inwardQty |issueQty 10100 coil 90 120 112233 abc 45 60 123456 coper 30 20最满意答案
问题在于,您要将每一行与每一行匹配,以issue ,因为他们正在处理相同的部分。 我认为子查询在这里是最好的:
select s.part_no,s.part_name,i.qty as inwardQty,p.qty as issueQty from stock s left join (select part_no,sum(qty) as qty from inward group by part_no) i on s.part_no = i.part_no left join (select part_no,sum(qty) as qty from issue group by part_no) p on s.part_no = p.part_no所以现在,每个连接中只有一个(或零)行加入,并且您没有获得笛卡尔积。
The problem is that you're matching every row for inward with every row for issue, for which they're dealing with the same part. I think subqueries would be best here:
select s.part_no,s.part_name,i.qty as inwardQty,p.qty as issueQty from stock s left join (select part_no,sum(qty) as qty from inward group by part_no) i on s.part_no = i.part_no left join (select part_no,sum(qty) as qty from issue group by part_no) p on s.part_no = p.part_noSo now, there's only one (or zero) rows to join in each of the joins, and you don't get a cartesian product.
更多推荐
发布评论