sql - 您如何找到一条链的原始 ID、第一个 ID 和最后一个 ID？

Question

请不要在未阅读问题的情况下将问题标记为重复。我确实发布了一个类似的问题，但 STACKOVERFLOW 社区成员要求我单独重新发布修改后的问题，因为考虑到一个小而微妙的修改，解决方案要复杂得多。

假设您有以下架构:

CREATE TABLE Data
(
  ID INT,
  CXL INT
 )

 INSERT Data (ID, CXL)
 VALUES
 (1, NULL),
 (2, 1),
 (3, 2),
 (5, 3),
 (6, NULL),
 (7, NULL),
 (8, 7);

CXL 列是取消特定ID 的ID。因此，例如，表中 ID:1 的第一行在被 ID:2(CXL 列)取消之前是好的。 ID:2 在被 ID:3 取消之前一直很好。 ID:3 在被 ID:5 取消之前一直很好，所以在这个序列中最后一个“好”的 ID 是 ID:5。

对于每个 ID，我想找到“GOOD”ID 和每个链的原始 ID。所以在这个例子中它将是:

ID   | Original ID | Latest GOOD ID
 1          1              5
 2          1              5
 3          1              5
 5          1              5
 6          6              6
 7          7              8
 8          7              8 

如果你想玩这个，这里有一个 fiddle : http://sqlfiddle.com/#!6/b794f/1

Answer 1

这应该给你你想要的:

;WITH CTE AS (
   SELECT ID AS Parent, ID, CXL, 0 AS level
   FROM Data
   WHERE CXL IS NULL

   UNION ALL 

   SELECT c.Parent AS Parent, d.ID, d.CXL, level = level + 1
   FROM CTE AS c
   INNER JOIN Data AS d ON c.ID = d.CXL
)
SELECT ID, Parent AS OriginalID, t.GoodID
FROM CTE AS c
CROSS APPLY (
   SELECT ID AS GoodID
   FROM CTE AS c2
   WHERE level = (SELECT MAX(level) FROM CTE WHERE Parent = c.Parent)
         AND c2.Parent = c.Parent
)  t(GoodID)
ORDER BY Parent

SQL Fiddle Demo