Sql递归查看上下级，根据上下级显示长名字

1、递归查看上级


	WITH CTE AS
	(
	--子项
		
		select 1 XuHao,id,departmentname,supdepid,tlevel from HrmDepartment where canceled=0 and id=@id
		
		UNION ALL
		--递归结果集中的上级

		SELECT b.XuHao+1 AS XuHao,a.id,a.departmentname,a.supdepid,a.tlevel 
			FROM HrmDepartment a
			INNER JOIN CTE b ON b.supdepid=a.id
		where a.canceled=0

	)
	SELECT s.* from  CTE s

2、递归查询上级后，用’/’拼接

WITH DirectReports(id,name,supdepid,longname)
AS
(
select d1.id,departmentmark name,supdepid,CONVERT (varchar(1000),departmentmark) longname
from hrmdepartment d1 where 1=1 and supdepid=0
UNION ALL
select d2.id,d2.departmentmark,d2.supdepid,CONVERT (varchar(1000),d.longname+'\'+d2.departmentmark)
from hrmdepartment d2 
inner join DirectReports AS d on d2.supdepid=d.id

)

select @DepartLongName=longname from DirectReports where id=@DepartId

3、递归查询，并输出最大

WITH CTE AS
(
	--子项
select 1 as RowNum,id,InstitutionHis,name,Version,id TopId from uf_db_Institution 
where InstitutionHis is null
UNION ALL
		--递归结果集中的上级

		SELECT b.RowNum+1 AS RowNum,a.id,a.InstitutionHis,a.name,a.Version,b.TopId 
			FROM uf_db_Institution a
			INNER JOIN CTE b ON b.id=a.InstitutionHis
		where a.InstitutionHis is not null
)
	SELECT s.* from  CTE s 
	where 1=1 and not exists(
	select 1 from CTE s2 where 1=1
	and s.TopId=s2.Topid 
	and s.RowNum<s2.RowNum
	)

打赏赞