连接表时的值错误 [英] Wrong values while joining tables
本文介绍了连接表时的值错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的桌子是
父母WO
WO PRICE
1 1790
1 9
儿童WO
WO PRICE
1 200
1 400
1 600
1 100
我尝试过:
< pre>我正在尝试这样做
从Parent_WO中选择总和(p.price),SUM(c.price)p
左外连接Child_WO c
on p.WO = c.WO
group by p.WO
我得到错误的p.price值。我得到的是7196,它应该是1796年。它是父母WO与4孩子WO的总数的多重。
解决方案
检查这个:
DECLARE @ Parent_WO TABLE (WO INT ,PRICE INT )
INSERT INTO @ Parent_WO (WO ,PRICE)
VALUES ( 1 , 1790 ),
( 1 , 9 )
DECLARE @ Child_WO TABLE (WO < span class =code-keyword> INT ,PRICE INT )
INSERT INTO @ Child_WO (WO,PRICE)
VALUES ( 1 , 200 ),
( 1 , 400 ),
( 1 , 600 ),
( 1 ,< span class =code-digit> 100 )
SELECT p.WO,p.TotalParent,c.TotalChild
FROM ( SELECT WO,SUM(PRICE) AS TotalParent FROM @ Parent_WO GROUP BY WO) AS p
INNER J. OIN ( SELECT WO,SUM(PRICE) AS TotalChild FROM @ Child_WO GROUP BY WO) AS c ON p.WO = c.WO
查询根据您的数据返回正确的结果。
您有两个具有WO = 1
的父行。您的JOIN
将与WO = 1
的四个子行匹配。
WO父母子女
---------------------
1 1790 200
1 1790 400
1 1790 600
1 1790 100
1 9 200
1 9 400
1 9 600
1 9 100
GROUP BY
然后将所有八行组合在一起,总计为:
WO父母子女
---------------------
1 7196 3900
MY tables are Parent WO WO PRICE 1 1790 1 9 Child WO WO PRICE 1 200 1 400 1 600 1 100 What I have tried: <pre>I am trying to do this Select sum(p.price), SUM(c.price) from Parent_WO p left outer join Child_WO c on p.WO= c.WO group by p.WO I am getting the wrong value for the p.price. I am getting 7196 when it should by 1796. It's multipe the total of the parent WO with the 4 child WO.
解决方案
Check this:
DECLARE @Parent_WO TABLE(WO INT, PRICE INT) INSERT INTO @Parent_WO(WO, PRICE) VALUES(1, 1790), (1, 9) DECLARE @Child_WO TABLE(WO INT, PRICE INT) INSERT INTO @Child_WO(WO, PRICE) VALUES(1, 200), (1, 400), (1, 600), (1, 100) SELECT p.WO, p.TotalParent, c.TotalChild FROM (SELECT WO, SUM(PRICE) AS TotalParent FROM @Parent_WO GROUP BY WO) AS p INNER JOIN (SELECT WO, SUM(PRICE) AS TotalChild FROM @Child_WO GROUP BY WO) AS c ON p.WO = c.WO
The query is returning the correct result based on your data.
You have two parent rows withWO = 1
. YourJOIN
will match both of those to the four child rows withWO = 1
.
WO Parent Child --------------------- 1 1790 200 1 1790 400 1 1790 600 1 1790 100 1 9 200 1 9 400 1 9 600 1 9 100
TheGROUP BY
will then group all eight rows together, giving you totals of:
WO Parent Child --------------------- 1 7196 3900
这篇关于连接表时的值错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文