sql server with 语句
递归，用于部门的拼接。eg：
WITH orgsAS (
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 pnamefrom orgswhere 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
页:
[1]