SQL Server 2008 CTE递归

收藏幸福

我正在尝试执行我认为使用CTE进行递归比较困难的事情是SQL Server 2008。
' D+ ?8 e! N$ i% I# I4 C在下面的示例中，您可以假设固定深度为3 …没有任何比这更低的深度了。在现实生活中，深度是“更深的”，但仍然是固定的。在示例中，我尝试将其简化一些。
我的输入数据如下。
8 j( P1 _2 M) ?+ o2 m6 K; J" J# R, JID     PARENT_ID       NAME          DEPTH
------------------------------------------
2 p% L1 W9 Y% ?; t1      NULL            A             1
: w7 }/ I+ v6 m: H+ r1 L2      1               B             2
3      2               C             3
8 E3 E% b7 U1 g( q2 |1 A4      1               D             2
我的CTE的输出应为下表。
LEVEL1_ID    LEVEL2_ID    LEVEL3_ID    LEVEL1_NAME    LEVEL2_NAME    LEVEL3_NAME
' T, \4 E( q" G- d! \9 ^--------------------------------------------------------------------------------
1            NULL         NULL         A              NULL           NULL
" V& i1 {- \. [1 m2 M1            2            NULL         A              B              NULL
1            2            3            A              B              C
+ i8 o: l6 o& N1            4            NULL         A              D              NULL
如果我可以在输出中获得ID列，则可以肯定地可以映射到查找表中的名称。
我也乐于接受其他方法来完成此任务，包括使用SSIS。
               
! i  L! y2 c' E: i解决方案:
               
% `0 b* A) P( C. E2 T

                并不是真的那么难做：
- ]) u' k1 z2 f+ D;WITH cte AS
8 D1 ]0 o1 b$ W(
    SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
5 y  e( Q9 y. u    FROM dbo.YourTable
    WHERE parent_id IS NULL
) o3 r" p1 j1 G! {( r    UNION ALL
    SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
    FROM dbo.YourTable t
: g/ Q0 r* ~3 z' k& [' J    INNER JOIN cte ON t.parent_id = cte.id
" ~( M  Y( Q2 z: G)
SELECT cteName FROM cte
' L5 h# e! y4 n# |ORDER BY ID
给我输出：
9 v! X) G, g% f; u' Z2 {5 k/A
/A/B
4 a7 w! C7 K* c0 @( _4 O3 V/A/B/C
! z3 `" [1 h- W0 M$ B/A/D
附带说明：“深度”可以由CTE轻松计算，您不必将其存储在表中（请参阅Level我添加的列）：
;WITH cte AS
(
    SELECT
8 h' x4 s" B$ ?9 P/ X9 l7 R) A$ [       CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID,
       1 AS 'Level'
    FROM dbo.YourTable
, c- c; @) D0 l, n  c    WHERE parent_id IS NULL
    UNION ALL
    SELECT
' b; f9 e0 m! }1 m5 w       CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID,
       cte.Level + 1 AS 'Level'
/ k" F9 z* I! ^. k    FROM dbo.YourTable t
& }/ o2 p; E4 X+ v    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
4 g# Z7 F& J. fORDER BY Level, ID