22,207
社区成员
发帖
与我相关
我的任务
分享
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] nvarchar(2),[PNAME] nvarchar(4),[PID] int)
Insert #T
select N'1',N'钢质',0 union all
select N'2',N'铜质',0 union all
select N'3',N'铁质',0 union all
select N'4',N'螺帽',1 union all
select N'5',N'螺丝',1 union all
select N'6',N'钉',1 union all
select N'7',N'办公用品',1 union all
select N'8',N'生活用品',1 union all
select N'9',N'桌子',7 union all
select N'10',N'水龙头',8 union all
select N'11',N'椅子',7
Go
;with b
as
(
Select *,cast('|---' as nvarchar(1000)) as Show,ord=cast(right(1000+ID,3) as nvarchar(50)) from #T where PID=0
union all
select a.*,cast('| '+b.Show as nvarchar(1000)) as show,ord=cast(b.ord+right(1000+a.ID,3)as nvarchar(50))
from #T as a inner join b on a.PID=b.ID
)
select Show+[PNAME] from b order by ord
/*
|---钢质
| |---螺帽
| |---螺丝
| |---钉
| |---办公用品
| | |---桌子
| | |---椅子
| |---生活用品
| | |---水龙头
|---铜质
|---铁质
*/