sql with 的妙用 

with aa(a,b,c)as
(
    select PROJECT_ID,PROJECT_NAME,PARENT_PROJECT_ID from T_PROJECT --where PARENT_PROJECT_ID is not null
),mm(PROJECT_ID,PROJECT_NAME,PARENT_PROJECT_ID,PARENT_PROJECT_NAME) as
(
    select distinct aa.a,aa.b,aa.c,p.PROJECT_NAME from aa
    left join T_PROJECT p on aa.c = p.PROJECT_ID
)
select * from mm

;
with aa(a,b,c)as
(
    select PROJECT_ID,PROJECT_NAME,PARENT_PROJECT_ID from T_PROJECT where PARENT_PROJECT_ID is not null
    union all 
    select p.PROJECT_ID,p.PROJECT_NAME,p.PARENT_PROJECT_ID from T_PROJECT p ,aa where p.PROJECT_ID = aa.c
)
select distinct * from aa
posted @ 2011-07-28 16:50  Shikyoh  阅读(513)  评论(0)  编辑  收藏  举报