查找列链值 [英] 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屋!