create table tb1
(
id int,
name varchar(50),
pid int
)
insert into tb1
select 1 ,'' ,0 union all
select 4 ,'' , 1 union all
select 5 ,'' , 1 union all
select 7 ,'' , 5 union all
select 8 ,'' , 1 union all
select 6 ,'' , 2 union all
select 2 ,'' , 0 union all
select 3 ,'' , 0
WITH tab as
(
SELECT *,0 level FROM TB1 WHERE pid=0
UNION ALL
SELECT A.*,b.level+1 FROM TB1 A,tab b
where a.pid = b.id
)
SELECT * from tab
----------------------------------
结果.....
--------------------------------
1 0 0
2 0 0
3 0 0
6 2 1
4 1 1
5 1 1
8 1 1
7 5 2
-------------------
想要结果.....有排序
id name pid
1 '' 0
4 '' 1
5 '' 1
7 '' 5
8 '' 1
6 '' 2
2 '' 0
3 '' 0
---------------------------------------
就是先显示一个父级 然后在显示它下面的子级(子级也可能是其他父级)
这个父级都显示完了 在显示下一个父级
递归会 但排序硬是没弄出来 求帮帮忙
3 个解决方案
#1
create table tb1
(
id int,
name varchar(50),
pid int
)
insert into tb1
select 1 ,'' ,0 union all
select 4 ,'' , 1 union all
select 5 ,'' , 1 union all
select 7 ,'' , 5 union all
select 8 ,'' , 1 union all
select 6 ,'' , 2 union all
select 2 ,'' , 0 union all
select 3 ,'' , 0
;with t
as(
select px=row_number()over(order by getdate()),
* from tb1
)
, tab as
(
SELECT *,0 level FROM t WHERE pid=0
UNION ALL
SELECT A.*,b.level+1 FROM t A,tab b
where a.pid = b.id
)
SELECT id,name,pid from tab order by px
/*
id name pid
----------- -------------------------------------------------- -----------
1 0
4 1
5 1
7 5
8 1
6 2
2 0
3 0
(8 行受影响)
*/
#2
目測你要的結果是這樣
;WITH tab as
(
SELECT id AS iden,*,0 level FROM TB1 WHERE pid=0
UNION ALL
SELECT iden,A.*,b.level+1 FROM TB1 A,tab b
where a.pid = b.id
)
SELECT id,name,pid,level from tab
ORDER BY iden,id
/*
id name pid level
--- ------ ------ -----
1 0 0
4 1 1
5 1 1
7 5 2
8 1 1
2 0 0
6 2 1
3 0 0
*/
#3
非常感谢2位！
#1
create table tb1
(
id int,
name varchar(50),
pid int
)
insert into tb1
select 1 ,'' ,0 union all
select 4 ,'' , 1 union all
select 5 ,'' , 1 union all
select 7 ,'' , 5 union all
select 8 ,'' , 1 union all
select 6 ,'' , 2 union all
select 2 ,'' , 0 union all
select 3 ,'' , 0
;with t
as(
select px=row_number()over(order by getdate()),
* from tb1
)
, tab as
(
SELECT *,0 level FROM t WHERE pid=0
UNION ALL
SELECT A.*,b.level+1 FROM t A,tab b
where a.pid = b.id
)
SELECT id,name,pid from tab order by px
/*
id name pid
----------- -------------------------------------------------- -----------
1 0
4 1
5 1
7 5
8 1
6 2
2 0
3 0
(8 行受影响)
*/
#2
目測你要的結果是這樣
;WITH tab as
(
SELECT id AS iden,*,0 level FROM TB1 WHERE pid=0
UNION ALL
SELECT iden,A.*,b.level+1 FROM TB1 A,tab b
where a.pid = b.id
)
SELECT id,name,pid,level from tab
ORDER BY iden,id
/*
id name pid level
--- ------ ------ -----
1 0 0
4 1 1
5 1 1
7 5 2
8 1 1
2 0 0
6 2 1
3 0 0
*/
#3
非常感谢2位！