sqlserver父子层次查询.pdf
需积分: 9 43 浏览量
2022-10-08
16:33:38
上传
评论
收藏 20KB PDF 举报
sqlserver⽗⼦层次查询
WITH DeptTree
as
(
select No,Name,parentNo,0 as Level,cast('0' as nvarchar(max)) as treepath from Port_Dept where No = '100'
union all
select d.No,d.Name,d.parentNo,tr.[Level] + 1,tr.treepath + [dbo].[Lpad](Row_Number() over (order by tr.No desc),8) as
treepath from Port_Dept as d
inner join DeptTree as tr on d.parentNo = tr.No )
SELECT No,replicate(' ',Level*3)+Name as Name,treepath from DeptTree order by treepath
--左端补指定数量的字符
create FUNCTION dbo.Lpad
(
@i int,@len int
)
RETURNS nvarchar(max)
AS
BEGIN
RETURN cast (replicate('0', @len - len(@i) ) + convert(nvarchar,@i) as nvarchar(max))
资源评论
