sql server with 语句

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