SQLserver数据库中关于递归的查询使用

王爱东 · 发表于 2020-3-19 13:32:49

度量快速开发平台数据库支持sqlserver数据库，我们之前习惯的oracle递归查询用的 start with dept_id=1000 connect by prior dept_id=upper_id的方式就不灵了。
比如我们的组织机构里面有很多下级机构及部门，要查询出登录人所在的机构，并列出该机构下所有机构和部门。Sqlserver写法如下：

with NewTable as
(
select a.dept_id,a.dept_name,a.upper_id,a.tree_code,a.extend_type,a.DEPT_LEVEL,a.SORT_ORDER from SA_DEPT_DICT a where a.DEPT_ID=登录用户的部门ID
union all
select b.dept_id,b.dept_name,b.upper_id,b.tree_code,b.extend_type,b.DEPT_LEVEL,b.SORT_ORDER from SA_DEPT_DICT b inner join NewTable c on b.DEPT_ID=c.UPPER_ID
where b.dept_TYPE=2 or b.DEPT_TYPE=6
)
,
NewTable1 as
(
select a.dept_id,a.dept_name,a.upper_id,a.tree_code,a.extend_type,a.DEPT_LEVEL,a.SORT_ORDER from SA_DEPT_DICT a , (select right(MAX(tree_code),4) dept_ID from NewTable where EXTEND_TYPE='机构' ) c where a.DEPT_ID=c.dept_ID
union all
select b.dept_id,b.dept_name,b.upper_id,b.tree_code,b.extend_type,b.DEPT_LEVEL,b.SORT_ORDER from SA_DEPT_DICT b inner join NewTable1 c on c.DEPT_ID=b.UPPER_ID
where b.dept_TYPE=2 or b.DEPT_TYPE=6
)
select DEPT_ID,DEPT_NAME,UPPER_ID,SORT_ORDER,EXTEND_TYPE,TREE_CODE from NewTable1