sql server with 语句

xajh32y

递归，用于部门的拼接。
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