SQL Server 2008 R2:准备递归查询 [英] SQL Server 2008 R2: Prepare a recursive query

问题描述

我的表格有两列，分别是 cola 和 colb，如下所示:

I have the table with the two columns namely cola and colb as shown below:

表:测试

create table Test
(
   cola int,
   colb int
);

我输入的记录是:

Cola   Colb
------------
1      2
1      3
1      4
2      5
2      6
2      3
3      2
3      4
3      7
3      10
10     11
11     12
11     13
11     14
12     15
13     16 
14     99
15     88
16     77

注意:现在我想显示唯一与我通过的值相关的记录.例如，如果我将值作为 1 传递，那么它应该向我显示连接到它的数字并像树一样形成连接.

Note: Now I want to show the only records who are connected with value I have pass. For example If I pass the value as 1 then it should display me the connected number to it and form connect like a tree.

为此，我使用以下脚本:

For this I am using the following script:

WITH CTE
AS
(
    SELECT Cola,Colb 
    FROM Test
    WHERE Cola IN 
    (
        SELECT Colb AS Colb
        FROM Test 
        WHERE Cola = '1'
    ) 
),
outerCTE1 AS
( 
    SELECT Cola,Colb FROM CTE 
    UNION
    SELECT Cola,Colb FROM Test
    WHERE Cola IN (SELECT Colb FROM CTE)
),
OuterCTE2 AS
(
    SELECT Cola,Colb FROM OuterCTE1
    WHERE Colb NOT IN (SELECT Cola FROM CTE)
    UNION
    SELECT Cola,Colb
    FROM Test
    WHERE Cola = '1'
),
lastCTE AS
(   
    SELECT Cola,Colb,ROW_NUMBER() OVER(PARTITION BY Colb ORDER BY Cola) rn FROM outerCTE2
)
SELECT Cola,Colb FROM lastCTE
WHERE rn <=1
ORDER BY CASE WHEN Cola = '1' THEN 1 ELSE 2 END,Cola;

但是:问题是我只能生成到 11 点的记录，但我想生成所有记录直到最后找到的匹配项.那么针对这种情况如何编写递归查询呢?

But: The problem is that I am just able to produce records till the 11 but I want to produce all the records till the end whichever match found till the end. So how to write a recursive query for this situation?

推荐答案

    ;WITH CTE AS
    (
       SELECT COLA,COLB,','+CAST(COLA AS VARCHAR(MAX))+',' AS CHCK FROM TEST WHERE COLA=1
       UNION ALL
       SELECT C1.COLA,C1.COLB,C.CHCK+CAST(C1.cola AS VARCHAR(MAX))+','
       FROM CTE C INNER JOIN TEST C1 ON  C.colb = C1.cola
       WHERE CHARINDEX(','+CAST(C.colb AS VARCHAR(MAX))+',',C.CHCK)=0
    )

   SELECT DISTINCT COLA,COLB FROM CTE ORDER BY COLA

这篇关于SQL Server 2008 R2:准备递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！