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 20

I 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 50

inward table :

part_no | qty 123456 10 123456 20 10100 20 112233 15 10100 25

issue table :

part_no | qty 112233 20 112233 15 123456 10 112233 25 10100 40 10100 20

my desired output :

part_no | part_name |inwardQty |issueQty 10100 coil 45 60 112233 abc 15 60 123456 coper 30 10

following 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_name

getting 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_no

So now, there's only one (or zero) rows to join in each of the joins, and you don't get a cartesian product.

更多推荐