基于其他表中行关系的SQL创建表

Question

需要帮助使用下表中的SQL ( Server)创建谱系表

Path ID  |  Sequence ID 
1            ad-1
1            ad-2
1            ad-3
2            ad-1
2            ad-4
3            ad-5
3            ad-6
3            ad-7
3            ad-8

预期的输出表对于路径中的每一对序列in都有一行，因此，输出如下所示

Path        |     Source Seq ID |     Target Seq ID
1                 ad-1                ad-2
1                 ad-2                ad-3
2                 ad-1                ad-4
3                 ad-5                ad-6
3                 ad-6                ad-7
3                 ad-7                ad-8

Answer 1

如果您使用的是Server 2012+：

WITH VTE AS (
    SELECT *
    FROM (VALUES (1,'ad-1'),
                 (1,'ad-2'),
                 (1,'ad-3'),
                 (2,'ad-1'),
                 (2,'ad-4'),
                 (3,'ad-5'),
                 (3,'ad-6'),
                 (3,'ad-7'),
                 (3,'ad-8')) V(PathID, SequenceID)),
Targets AS (
    SELECT PathID,
           SequenceID,
           LEAD(SequenceID) OVER (PARTITION BY PathID ORDER BY SequenceID ASC) AS TargetSequenceID
    FROM VTE)
SELECT *
FROM Targets
WHERE TargetSequenceID IS NOT NULL;