Find chain of columns values

问题:

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 

回答1:

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;