db2 再帰sql

with p (org_id, APPROVAL_REVEL, USER_ID, user_name, APPROVAL_USER, APPROVAL_name, level )
as (
select
t1.USER_ID as org_id, t1.APPROVAL_REVEL, t1.USER_ID, t1.user_name, t1.APPROVAL_USER, t2.USER_NAME as APPROVAL_name
,0 as level
from t_user t1 left outer join t_user t2
on t1.APPROVAL_USER = t2.USER_ID
where t1.USER_ID = 1
union all
select
p.org_id,
c.*,
p.level +1 as level
from
(
select
s1.APPROVAL_REVEL, s1.USER_ID, s1.user_name, s1.APPROVAL_USER, s2.USER_NAME as APPROVAL_name
from t_user s1 left outer join t_user s2
on s1.APPROVAL_USER = s2.USER_ID
) c, p
where
p.APPROVAL_USER = c.USER_ID and p.APPROVAL_USER <> 1)
select * from p
order by APPROVAL_REVEL