34,575
社区成员
发帖
与我相关
我的任务
分享
create table tb(id varchar(3) , name varchar(3) , pid varchar(3))
insert into tb values('1' , 'a' , '0')
insert into tb values('2' , 'b' , '0')
insert into tb values('3' , 'c' , '0')
insert into tb values('4' , 'd' , '1')
insert into tb values('5' , 'e' , '1')
insert into tb values('6' , 'f' , '2')
insert into tb values('7' , 'g' , '3')
insert into tb values('8' , 'h' , '3')
go
select '-' + a.name name , a.id , a.id px from tb a where a.pid = '0'
union all
select '---' + b.name name , a.id , b.id px from tb a , tb b where a.id = b.pid
order by id , px
drop table tb
/*
name id px
------ ---- ----
-a 1 1
---d 1 4
---e 1 5
-b 2 2
---f 2 6
-c 3 3
---g 3 7
---h 3 8
(所影响的行数为 8 行)
*/
-- sql 2005 及之后的版本可以直接查询
-- 示例数据
DECLARE @t TABLE(
id int,
name varchar(10),
typeid int
)
INSERT @t
SELECT 1, 'a', 0 UNION ALL
SELECT 2, 'b', 0 UNION ALL
SELECT 3, 'c', 0 UNION ALL
SELECT 4, 'd', 1 UNION ALL
SELECT 5, 'e', 1 UNION ALL
SELECT 6, 'f', 2 UNION ALL
SELECT 7, 'g', 3 UNION ALL
SELECT 8, 'h', 3
-- 查询
;WITH
TREE AS(
SELECT
*,
path = CONVERT(varchar(8000), RIGHT(1000 + id, 3)),
level = 0
FROM @t
WHERE typeid = 0
UNION ALL
SELECT
A.*,
path = CONVERT(varchar(8000), B.path + RIGHT(1000 + A.id, 3)),
level = B.level + 1
FROM @t A, TREE B
WHERE A.typeid = B.id
)
SELECT
REPLICATE('-', level * 4) + name
FROM TREE
ORDER BY path