组织机构sql

最新推荐文章于 2023-08-04 17:25:59 发布

weixin_30653023

最新推荐文章于 2023-08-04 17:25:59 发布

阅读量370

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/shijun/p/4923158.html

with cte as
(
    select vcOrganID, vcParentID, vcOrganName, 0 as lvl from tbOrgan
    where vcOrganID = 2
    union all
    select d.vcOrganID, d.vcParentID, d.vcOrganName, lvl+1
     from cte c inner join tbOrgan d on c.vcOrganID = d.vcParentID
)
select * from cte

select
    vcOrganID, vcParentID, vcOrganName,vcOrder,
    STUFF((select ',' + vcOrganID from tbOrgan
            where o.vcOrder like vcOrder+'%' and o.vcOrder<>vcOrder
            order by vcOrder
            FOR XML PATH('')), 1, 1, '') as parentIds
from tbOrgan o
order by vcOrder

转载于:https://www.cnblogs.com/shijun/p/4923158.html