递归,用于部门的拼接。
eg:
WITH orgs AS (
SELECT o.org_id , o.org_name, o.ORG_PARENT , CAST('/'+ o.ORG_NAME as varchar(4000)) as pname from U_ORG o where o.org_id = 1
union all
SELECT s.ORG_ID , s.ORG_NAME , s.ORG_PARENT , cast(s.ORG_NAME + '/'+b.pname as varchar(4000)) from U_ORG s , orgs b
where s.ORG_parent = b.org_id )
select * from orgs
或
WITH orgs(id , name , parent ) AS (
SELECT o.org_id id , o.org_name name, o.ORG_PARENT parent from U_ORG o
) ,
s (id , name , parent , pname ) AS(
SELECT id , name , parent , CAST('/'+ name as varchar(4000)) as pname from orgs where id = 1 UNION all
SELECT b.id , b.name , b.parent , cast(s.pname+ '/'+b.name as varchar(4000)) FROM s , orgs b WHERE s.id = b.parent
)
select * from s
eg:
WITH orgs AS (
SELECT o.org_id , o.org_name, o.ORG_PARENT , CAST('/'+ o.ORG_NAME as varchar(4000)) as pname from U_ORG o where o.org_id = 1
union all
SELECT s.ORG_ID , s.ORG_NAME , s.ORG_PARENT , cast(s.ORG_NAME + '/'+b.pname as varchar(4000)) from U_ORG s , orgs b
where s.ORG_parent = b.org_id )
select * from orgs
或
WITH orgs(id , name , parent ) AS (
SELECT o.org_id id , o.org_name name, o.ORG_PARENT parent from U_ORG o
) ,
s (id , name , parent , pname ) AS(
SELECT id , name , parent , CAST('/'+ name as varchar(4000)) as pname from orgs where id = 1 UNION all
SELECT b.id , b.name , b.parent , cast(s.pname+ '/'+b.name as varchar(4000)) FROM s , orgs b WHERE s.id = b.parent
)
select * from s