时间戳

首页 > 解决方案 > SQL Server 查找两列之间的链

sql-server - SQL Server 查找两列之间的链

问题描述

我有一张这样的桌子：

from | to
-----+-----
 23  | 24
 24  | 25
 25  | 27
 27  | 30
 45  | 46
 46  | 47
 50  | 52
 53  | 60

我需要一个 SQL Server 查询来检测链并返回每个链中的最小值（从）和最大值（到）（也是具有一条记录的链）：

from | to
-----+-----
 23  | 30
 45  | 47
 50  | 52
 53  | 60

标签： sql-server

解决方案

这是一种使用递归 CTE 的方法。

CREATE TABLE #chainLinks(linkFrom INTEGER, linkTo INTEGER);
INSERT INTO #chainLinks VALUES (23,24);
INSERT INTO #chainLinks VALUES (24,25);
INSERT INTO #chainLinks VALUES (25,27);
INSERT INTO #chainLinks VALUES (27,30);
INSERT INTO #chainLinks VALUES (45,46);
INSERT INTO #chainLinks VALUES (46,47);
INSERT INTO #chainLinks VALUES (50,52);
INSERT INTO #chainLinks VALUES (53,60);

WITH reccte AS
(
    /*Recursive Seed*/
    SELECT linkFrom AS chainStart,
        linkFrom,
        linkTo,
        0 as links
    FROM #chainLinks as chainLinks
    WHERE linkFrom NOT IN (SELECT DISTINCT linkTo FROM #chainLinks)

    UNION ALL

    /*Recursive Term*/
    SELECT 
        reccte.chainStart,
        chainLinks.linkFrom,
        chainLinks.linkTo,
        links + 1
    FROM reccte
        INNER JOIN #chainLinks as chainLinks ON reccte.linkTo = chainLinks.linkFrom
)
SELECT chainStart, linkTo AS chainEnd
FROM
    (
        SELECT chainStart, linkFrom, linkTo, links, ROW_NUMBER() OVER (PARTITION BY chainStart ORDER BY links DESC) AS rn
        FROM reccte 
    )subrn
WHERE rn = 1;

递归 CTE 包含两个部分

  1. 递归种子 - 这是UNION我们确定表中哪些记录开始递归的部分。在这里，我们想要任何linkFrom不是linkTo
  2. 一个 recursive 术语 - 这是UNION我们加入cte 的下面的部分，reccte回调到原始表。这部分 CTE 反复迭代，直到连接失败。

在这里，我们还跟踪links只是我们为获得该输出记录而经历的迭代次数的计数器。links我们为每个起点保留最高数量chainStart。

这是工作示例：https ://rextester.com/JWUW57837

推荐阅读