sql 递归

redshift

CREATE OR  REPLACE view enriched_stage_ersdb.v_TCFG_CCHierarchy
as
with cteInit
as
(
SELECT   
H.HierarchyCode AS CCCode
,ISNULL(CC.Name, H.Description) AS CCName
, H.ParentCode AS CCParentCode,
          H.CreateTime as CreateTime
FROM      enriched_PRESTAGE_ERSDB.TCFG_CCHierarchy AS H LEFT OUTER JOIN
          enriched_PRESTAGE_ERSDB.TCFG_CostCenter AS CC 
          ON H.HierarchyCode = CC.Code AND H.ParentCode = CC.HierarchyCode 
),
cte_1
as
(
    select *,1 as HierarchyLevel from cteInit where CCParentCode is null
),
cte_2
as 
( 
    select cteInit.*, 2 from cteInit  
    inner join cte_1 on cteInit.CCParentCode=cte_1.CCCode
)
,
cte_3
as 
( 
    select cteInit.*, 3 from cteInit  
    inner join cte_2 on cteInit.CCParentCode=cte_2.CCCode
)
,
cte_4
as 
( 
    select cteInit.*, 4 from cteInit  
    inner join cte_3 on cteInit.CCParentCode=cte_3.CCCode
)
,
cte_5
as 
( 
    select cteInit.*, 5 from cteInit  
    inner join cte_4 on cteInit.CCParentCode=cte_4.CCCode
)
,
cte_6
as 
( 
    select cteInit.*, 6 from cteInit  
    inner join cte_5 on cteInit.CCParentCode=cte_5.CCCode
)
,
cte_7
as 
( 
    select cteInit.*, 7 from cteInit  
    inner join cte_6 on cteInit.CCParentCode=cte_6.CCCode
)
,
cte_8
as 
( 
    select cteInit.*, 8 from cteInit  
    inner join cte_7 on cteInit.CCParentCode=cte_7.CCCode
)
,
cte_9
as 
( 
    select *,1 as HierarchyLevel from cteInit where CCParentCode is null
    union all
       select cteInit.*, 2 from cteInit  
    inner join cte_1 on cteInit.CCParentCode=cte_1.CCCode
    union all 
    select cteInit.*, 3 from cteInit  
    inner join cte_2 on cteInit.CCParentCode=cte_2.CCCode
    union all 
    select cteInit.*, 4 from cteInit  
    inner join cte_3 on cteInit.CCParentCode=cte_3.CCCode
    union all
        select cteInit.*, 5 from cteInit  
    inner join cte_4 on cteInit.CCParentCode=cte_4.CCCode
    union all
        select cteInit.*, 6 from cteInit  
    inner join cte_5 on cteInit.CCParentCode=cte_5.CCCode
    union all
        select cteInit.*, 7 from cteInit  
    inner join cte_6 on cteInit.CCParentCode=cte_6.CCCode
    union all
        select cteInit.*, 8 from cteInit  
    inner join cte_7 on cteInit.CCParentCode=cte_7.CCCode
    union all
    select cteInit.*, 9 from cteInit  
    inner join cte_8 on cteInit.CCParentCode=cte_8.CCCode
)

select * from cte_9;
 

sql server

CREATE view [eRSDB].[v_TCFG_CCHierarchy]
as
with cteInit
as
(SELECT   H.HierarchyCode AS CCCode,ISNULL(CC.Name, H.Description) AS CCName
, H.ParentCode AS CCParentCode,
          H.CreateTime as CreateTime
FROM      [PRESTAGE].ERSDB.TCFG_CCHierarchy AS H LEFT OUTER JOIN
          [PRESTAGE].ERSDB.TCFG_CostCenter AS CC ON H.HierarchyCode = CC.Code AND H.ParentCode = CC.HierarchyCode 
),
cte
as
(
    select *,1 as [HierarchyLevel] from cteInit where CCParentCode is null
    union all
    select cteInit.*,[HierarchyLevel] +1  from cteInit  inner join cte on cteInit.CCParentCode=cte.CCCode
)

select * from cte