mysql - 查询从两个不同的年份列中减去值？

Question

我有一个返回数据集的查询，该数据集返回两个不同年份的结果。每个位置 ID 正好有两行(不一定按顺序):

+------+---------------------------------------+| year   | location_id |  unique_1  | data+------+---------------------------------------+| 1990   | 100         |  343       | 100| 2000   | 100         |  343       | 200| 1990   | 55          |  111       | 50| 2000   | 55          |  111       | 60

I want to take the results for each of the years and subtract the data column from the earlier year's from the data column from the later year's row.

Something like this (which would return 100 if this was actually valid MySQL syntax), but it would need to be for all rows:

(SELECT data FROM TABLE 
 WHERE year = 2000
 AND location_id = 100
 AND unique_1 = 343 )

MINUS

(SELECT data FROM TABLE 
 WHERE year = 1990
 AND location_id = 100
 AND unique_1 = 343 )

Answer 1

如果你保证同一 location_id 恰好有两行，你可以这样做:

select
    a.location_id
,   b.data - a.data
from test a
join test b on a.location_id=b.location_id and a.data>b.data

此查询确保具有相同位置 ID 的两行以这样一种方式连接在一起，即具有较小 data 的行位于 a 侧，而 b 在b 端。

Demo.