从两个不同的表中减去值

Consider table X:

考虑表X：

Consider table Y:

考虑表Y：

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

2 个解决方案

#1

It's not clear what you want. Could it be this?

目前尚不清楚你想要什么。可能是这个吗？

SELECT  (SELECT SUM(A) FROM X) - 
        (SELECT SUM(A) FROM Y)
        AS MyValue

#2

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

丹尼尔

#1