sqlserver 2005 公用表表达式CTE与 ORACLE递归生成树查询 对比

sqlserver 2005 公用表表达式CTE：

语法：with <name of you cte>(<column names>)

　　 as(

　 　<actual query>

　 　)

　 　select * from <name of your cte>

示例：with tableFor(id,COLUPARENTID) as (select id,COLUPARENTID 
from T_ZWGK_ZW_COLUMN where id='' union all select z.ID,z.COLUPARENTID 
from T_ZWGK_ZW_COLUMN z, tableFor t where  t.id=z.COLUPARENTID) 
select ID,COLUPARENTID from tableFor order by COLUPARENTID asc

ORACLE递归生成树：

语法：select * from (select t.orgid,t.parentid,t.orgname,LEVEL 层次
from um_organization t
connect by t.parentid = PRIOR t.orgid
start with t.orgid = 1
) where 层次 = 2

示例：select * from (select A.id,A.COLUPARENTID,LEVEL 层次 from 
T_ZWGK_ZW_COLUMN A connect by A.COLUPARENTID=PRIOR A.id start with A.id='')
where 层次=2