sql - 比较sql表中的行

Question

我有一个跟踪实体更改的表，我正在尝试提取这些更改。结构大致是这样的:

| RowNumber | Value | SourceID| TargetID |
|  1        | A     |   100   | 50       |
|  2        | B     |   100   | 100      |
|  3        | C     |   200   | 100      |

我的选择是

select t1.Value as Old, t2.Value as New from MyTable t1 
inner join MyTable t2 on t1.SourceID = t2.TargetID
where t1.value != t2.value

这给了我:

|Old|New|
|A  | B |
|A  | C |
|B  | C |

问题是，数据从 A->B 更改，然后从 B->C 更改。它从未真正从 A->C 改变过，而且我终其一生都找不到在一个查询中执行此操作的方法，我意识到游标可以按顺序遍历行来实现这一点。

这在一次查询中可能吗？

Answer 1

您可以使用 ROW_NUMBER 窗口函数来查找第一个下一个。

示例:

declare @MyTable table (RowNumber int primary key identity(1,1), [Value] varchar(30), SourceID int, TargetID int);

insert into @MyTable ([Value], SourceID, TargetID) values
('A', 100, 50),
('B', 100, 100),
('C', 200, 100);

SELECT Old, New
FROM
(
    select 
     t1.[Value] as Old, 
     t2.[Value] as New,
     row_number() over (partition by t1.RowNumber order by t2.RowNumber) as RN
    from @MyTable t1 
    join @MyTable t2 
      on t2.TargetID = t1.SourceID AND t2.RowNumber > t1.RowNumber
) q
WHERE RN = 1;

返回:

Old New
A   B
B   C