treeid parentid classname parentpath
1 0 a ,0,
2 0 b ,0,
3 1 ab ,0,1,
4 1 ac ,0,1,
5 2 ba ,0,2,
6 3 abc ,0,1,3,
显示如下 classname
a
ab
abc
ac
b
ba
1 0 a ,0,
2 0 b ,0,
3 1 ab ,0,1,
4 1 ac ,0,1,
5 2 ba ,0,2,
6 3 abc ,0,1,3,
显示如下 classname
a
ab
abc
ac
b
ba
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/20/4573387.aspx
你要的这个效果,只要按照classname排一下序,不就能得到想要的结果吗
类似吧
declare @TB table([id] int,[parentid] int,[classname] varchar(3),[parentpath] varchar(7))
insert @TB
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
;WITH SubsCTE
AS
(
SELECT [id], [classname], 0 AS lvl,
-- Path of root = '.' + empid + '.'
CAST(CAST([id] AS VARCHAR(10))
AS VARCHAR(MAX)) AS path
FROM @TB
UNION ALL
SELECT C.[id], C.[classname], P.lvl + 1, CAST(P.path+'-' + CAST(C.[id] AS VARCHAR(10))
AS VARCHAR(MAX)) AS path
FROM SubsCTE AS P
JOIN @TB AS C
ON C.[parentid] = P.[id]
)
SELECT [id], REPLICATE(' ', lvl) + [classname] AS empname
FROM SubsCTE
ORDER BY path;/*
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a
3 ab
6 abc
4 ac
2 b
5 ba
3 ab
6 abc
4 ac
5 ba
6 abc
这下结果完全对了
declare @TB table([id] int,[parentid] int,[classname] varchar(3),[parentpath] varchar(7))
insert @TB
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
;WITH SubsCTE
AS
(
SELECT [id], [classname], 0 AS lvl,
-- Path of root = '.' + empid + '.'
CAST(CAST([id] AS VARCHAR(10))
AS VARCHAR(MAX)) AS path
FROM @TB
WHERE [parentid] = 0
UNION ALL
SELECT C.[id], C.[classname], P.lvl + 1, CAST(P.path+'-' + CAST(C.[id] AS VARCHAR(10))
AS VARCHAR(MAX)) AS path
FROM SubsCTE AS P
JOIN @TB AS C
ON C.[parentid] = P.[id]
)
SELECT [id], (CASE WHEN lvl>0 THEN ' ' ELSE '' END) + [classname] AS empname
FROM SubsCTE
ORDER BY path;
/*
id empname
----------- -------
1 a
3 ab
6 abc
4 ac
2 b
5 ba(6 row(s) affected)
if object_id('[tree]') is not null drop table [tree]
create table [tree] (id int,parentid int,classname varchar(3),parentpath varchar(20))
insert into [tree]
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
go;with wsp
as
(
select px=id,* from tree where parentid=0
union all
select px=a.px,b.* from wsp a,tree b where a.id=b.parentid
)
select * from wsp order by px,id
--> 测试数据: [tree]
if object_id('[tree]') is not null drop table [tree]
create table [tree] (id int,parentid int,classname varchar(3),parentpath varchar(20))
insert into [tree]
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
go
;with wsp
as
(
select px=id,lev=cast(1 as varchar),* from tree b where parentid=0
union all
select px=a.px,lev=cast(a.lev+ltrim(row_number() over(order by b.id)) as varchar),b.* from wsp a,tree b where a.id=b.parentid
)
select classname from wsp order by px,lev--结果:
classname
---------
a
ab
abc
ac
b
ba