sql server 父子层次查询

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))