SQL递归查询上级部门树,sql2008以上支持with语法。
在做预算归口部门、使用部门的时候,需要递归查询出部门的逐级上级sParent。
SQL脚本:
with tmp as
(
select * from Sections where sID in
( select sItemID from LogUsersList where sID='00000' and sUsage = '预算部门')
UNION ALL
SELECT sections.* from Sections inner join tmp on sections.sID=tmp.sParent
)
SELECT distinct * FROM tmp order by sID asc
等效于递归,在with tmp中from tmp