Teradata 递归的实现

        今天使用了递归输出一个棵树，sql如下。

WITH RECURSIVE temp_table (DIM_VALUE_ID,DIM_VALUE_NAME,depth) AS
(SELECT  root.DIM_VALUE_ID,root.DIM_VALUE_NAME, 0 as depth
FROM TAP_C_GRP_DIM_VALUE root
WHERE root.PAR_DIM_VALUE_ID = '-1' and root.STATISTICS_TYPE_ID = 0
UNION ALL
SELECT  indirect.DIM_VALUE_ID,indirect.DIM_VALUE_NAME,direct.depth + 1 AS newdepth 
FROM temp_table direct, TAP_C_GRP_DIM_VALUE indirect
WHERE direct.DIM_VALUE_ID = indirect.PAR_DIM_VALUE_ID and indirect.STATISTICS_TYPE_ID = 0
)
SELECT DIM_VALUE_ID,DIM_VALUE_NAME,depth  FROM temp_table order by DIM_VALUE_ID;