设评论表结构及数据大至如下:
create table tb(id int identity(1,1),--id
article_id int,--文章id
parent_id int,--若为null表示为评论主题,不为null则表示为回复内容
title varchar(100),--标题
logtime datetime not null default getdate()
)
go
insert tb(article_id,parent_id,title) select 1,null,'test'
union all select 1,null,'test1'
union all select 1,2,'reply test1 1'
union all select 1,1,'reply test 2'
union all select 1,2, 'reply test1 2'
select isnull(right(rtrim(parent_id) + ' ',4),'')+title from tb order by isnull(parent_id,id),isnull(parent_id,0)
/*
test
reply test 2
test1
reply test1 1
reply test1 2*/drop table tb
go
create table tb(id int identity(1,1),--id
article_id int,--文章id
parent_id int,--若为null表示为评论主题,不为null则表示为回复内容
title varchar(100),--标题
logtime datetime not null default getdate()
)
go
insert tb(article_id,parent_id,title) select 1,null,'test'
union all select 1,null,'test1'
union all select 1,2,'reply test1 1'
union all select 1,1,'reply test 2'
union all select 1,2, 'reply test1 2'
select isnull(right(rtrim(parent_id) + ' ',4),'')+title from tb order by isnull(parent_id,id),isnull(parent_id,0)
/*
test
reply test 2
test1
reply test1 1
reply test1 2*/drop table tb
go
因为主题和回复应该是按照有先后的时间顺序的.
order by case when parentid=0 then id else parentid end,
case when parentid=0 then 1 else 2 end,id