sql - 如何通过组合 SQL 表中的字段来生成先前的值表？

Question

假设我在 SQL Server 数据库中有一些数据。

Location   PayID    Year
------------------------
Loc1        100     2010
Loc1        100     2011
Loc1        101     2012
Loc2        200     2010
Loc2        201     2011
Loc2        202     2012

我正在尝试在 SQL Server 中编写一个查询，该查询将为我提供一个包含两列的表，我可以通过搜索来查找特定位置的先前 PayID。所以输出是。

PayID   PrevID
-----------------
101     100
202     201
201     200

当上一年的 ID 与当前年份不同时，它只需要一个条目，如果用户返回一年以上时没有正确的匹配项，我将递归查询它，因此它将提取每个以前的 ID基于刚刚拉取的那个，直到找到与第一个表匹配的 PayID 和 Year。

对此的任何帮助将不胜感激。我会仔细搜索，如果找到的话会发布解决方案。

Answer 1

这可以通过递归 CTE 轻松完成:

with cte as (
  select Location, PayID, PayID as PrevID, Year from payhistory
  union all
  select p.Location, p.PayID, cte.PayID as PrevID, p.Year
  from payhistory p
  join cte on cte.Location = p.Location and cte.Year + 1 = p.Year
)
select distinct Location, PayID, PrevID
from cte
where PayID <> PrevID;

这是我得到的结果:

| LOCATION | PAYID | PREVID |
|----------|-------|--------|
|     Loc1 |   101 |    100 |
|     Loc2 |   201 |    200 |
|     Loc2 |   202 |    201 |

演示:http://www.sqlfiddle.com/#!3/e0ac0/4