SQL Server 2008 R2:递归查询 [英] SQL Server 2008 R2: Recursive query

问题描述

这是以下的后续问题:准备递归查询

我的表格中有两列，分别是可乐和可乐，如下所示:

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 the above requirement I have got the script from Dark Knight as shown below which works fine.

;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
),
OUTERCTE AS
(   
    SELECT DISTINCT COLA,COLB,ROW_NUMBER() OVER(PARTITION BY Colb ORDER BY Cola) rn FROM CTE --ORDER BY COLA
)
SELECT Cola,Colb FROM OUTERCTE
WHERE rn<=1
ORDER BY CASE WHEN Cola = 1 THEN 1 ELSE 2 END;

这给了我这个:

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

要求:现在我想显示记录的级别.

Requirement: Now I want to show the levels of records.

预期结果:

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

推荐答案

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

),
cte2 as (
select * , ROW_NUMBER() over (partition by colb order by lvl)as rn From CTE
)
select cola,colb,lvl from cte2 where rn = 1

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