我有一个表如下:comment_tableid body post_id parent_comment_id
1 a 1 null
2 b 1 null
3 c 1 1
4 d 1 2
我想要如下的结果a
c
b
d应该怎么做??我对SQL不是很熟,查了一天了都无解。 多谢!!
1 a 1 null
2 b 1 null
3 c 1 1
4 d 1 2
我想要如下的结果a
c
b
d应该怎么做??我对SQL不是很熟,查了一天了都无解。 多谢!!
Union all select * from tb where id=3
union all select * from tb where id=2
union all select * from tb where id=4
(id int, body varchar(5), post_id int, parent_comment_id int)
insert into comment_table select 1,'a',1,null
insert into comment_table select 2,'b',1,null
insert into comment_table select 3,'c',1,1
insert into comment_table select 4,'d',1,2
goselect body from(
select id,body,1 as post from comment_table where parent_comment_id is null
union all
select parent_comment_id as id,body,2 as post from comment_table where parent_comment_id in
(select id FROM comment_table where parent_comment_id is null)
)t
order by id,postgo
drop table comment_table/*
body
-----
a
c
b
d
*/
insert into tb select 1,'a',null
insert into tb select 2,'b',null
insert into tb select 3,'c',1
insert into tb select 4,'d',2select id,body,parent_comment_id from tb
order by isnull(parent_comment_id,id),idid body parent_comment_id
1 a NULL
3 c 1
2 b NULL
4 d 2只有2级可以这样用,多级别用dom排序
drop table #temp1
drop table comment_tablecreate table comment_table
(id int, body varchar(5), post_id int, parent_comment_id int)
insert into comment_table select 1,'a',1,null
insert into comment_table select 2,'b',1,null
insert into comment_table select 3,'c',1,1
insert into comment_table select 4,'d',1,2
goselect 'X'=body,*,'fldlevel'=1 into #temp1 from comment_table where parent_comment_id is nulldeclare @i int
set @i=1while exists (select * from #temp1 where fldlevel=@i)
begin
insert into #temp1
select
b.X,
a.*,
'fldlevel'=@i+1
from comment_table a inner join #temp1 b on a.parent_comment_id=b.id and b.fldlevel=@i
set @i=@i+1
endselect Body from #temp1 order by X,fldlevel
create table comment_table
(id int, body varchar(5), post_id int, parent_comment_id int)
insert into comment_table select 1,'a',1,null
insert into comment_table select 2,'b',1,null
insert into comment_table select 3,'c',1,1
insert into comment_table select 4,'d',1,2
gowith
xwj
as
(select id,body,post_id,parent_comment_id ,level=1,body as part from comment_table where parent_comment_id is null
union all
select a.id,a.body,a.post_id,a.parent_comment_id,b.level+1 as level , b.part from comment_table a
inner join xwj b on a.parent_comment_id=b.id
)
select id,body,post_id,parent_comment_id from xwj order by part,level;
/*
id body post_id parent_comment_id
----------- ----- ----------- -----------------
1 a 1 NULL
3 c 1 1
2 b 1 NULL
4 d 1 2(4 行受影响)*/