查找列链值 [英] Find chain of columns values

问题描述

表格:

 创建表tbl_test(col1 int，col2 int); 

记录:

  INSERT INTO tbl_test VALUES(111,112)，(112,113)，(113,114)，(114,111)，(115,116)，(117,118)，(118,119)，(111,130)，(120,121)，(122,123)，(123,111)，(124,111)； 

查询:我想以 111-> 112-> 113-> 114-> 111 的形式找到完整的链col1和col2(这是完整的链，因为它以 111 开头并以 111 结尾).

预期的输出2:

  col1 col2-------------111112112113113114114111 

解决方案

此处有更多详细信息，以使其更容易理解.首先，我们将使用递归

Table:

create table tbl_test
(
    col1 int,
    col2 int
);

Records:

INSERT INTO tbl_test VALUES(111,112),
                            (112,113),
                            (113,114),
                            (114,111),
                            (115,116),
                            (117,118),
                            (118,119),
                            (111,130),
                            (120,121),
                            (122,123),
                            (123,111),
                            (124,111);

Query: I want to find the complete chain col1 and col2 in the form of 111->112->113->114->111 (This is complete chain because it starts with 111 and end with 111).

Expected Output 2:

col1    col2
-------------
111     112
112     113
113     114
114     111 

解决方案

There are more details here in order to understand it easier. First of all, we are going to use a recursive common table expression. Then, we need to know the start value for each group in order to know when to stop the recursion. In your case this is the minimum value of col01, but we can have many start values, so I've added a group id - you can use it to filter the final results.

WITH DataSource AS
(
    SELECT col1
          ,col2
          ,0 as level
          ,ROW_NUMBER() OVER(ORDER BY Col1, col2) AS [groupID]
          ,0 as anchorMatched
          ,col1 as startValue
    FROM tbl_test
    WHERE col1 IN (SELECT MIN(col1) FROM tbl_test)
    UNION ALL
    SELECT A.col1
          ,A.col2
          ,level + 1
          ,B.[groupID]
          ,anchorMatched + CASE WHEN A.col1 = B.col2 AND A.col2 = B.startValue THEN 1 ELSE 0 END
          ,b.startValue
    FROM tbl_test A
    INNER JOIN DataSource B
        ON A.col1 = B.col2
    WHERE (anchorMatched = 0 AND A.col1 <> B.startValue)
)
SELECT *
FROM DataSource
WHERE groupID = 1;

这篇关于查找列链值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！