SQL 从两个不同的表中减去值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5254859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtract Values from Two Different Tables
提问by Boobie
Consider table X:
考虑表 X：
A
-
1
2
3
3
6
Consider table Y:
考虑表 Y：
A
-
0
4
2
1
9
How do you write a query that takes the difference between these two tables, to compute the following table (say table Z):
你如何编写一个查询来获取这两个表之间的差异，以计算下表（比如表 Z）：
A
-
1
-2
1
2
-3
回答by p.campbell
It's not clear what you want. Could it be this?
不清楚你想要什么。会是这个吗？
SELECT (SELECT SUM(A) FROM X) -
(SELECT SUM(A) FROM Y)
AS MyValue
回答by Daniel Williams
Marcelo is 100% right - in a true relational database the order of a result set is never guaranteed. that said, there are some databases that do always return sets in an order.
Marcelo 是 100% 正确的——在真正的关系数据库中，结果集的顺序永远无法保证。也就是说，有些数据库确实总是按顺序返回集合。
So if you are willing to risk it, here is one solution. Make two tables with autoincrement keys like this:
因此，如果您愿意冒险，这里有一个解决方案。使用自动增量键创建两个表，如下所示：
CREATE TABLE Sets (
id integer identity(1,1)
, val decimal
)
CREATE TABLE SetY (
id integer identity(1,1)
, val decimal
)
Then fill them with the X and Y values:
然后用 X 和 Y 值填充它们：
INSERT INTO Sets (val) (SELECT * FROM X)
INSERT INTO SetY (val) (SELECT * FROM Y)
Then you can do this to get your answer:
然后你可以这样做来得到你的答案：
SELECT X.ID, X.Val, Y.Val, X.val-Y.val as Difference
FROM Sets X
LEFT OUTER JOIN SetY Y
ON Y.id = X.ID
I would cross my fingers first though! If there is any way you can get a proper key in your table, please do so.
不过我会先交叉手指！如果有任何方法可以在您的表中获得正确的密钥，请这样做。
Cheers,
干杯，
Daniel
丹尼尔
