如何获得对bolg回复的再评论的查询Sql

Question

我设计了一个评论表，里面增加了一个评论字段，用于记录当前评论是对之前的那条回复做评论，但现在在做排序时，没法把这个评论插在回复之前。USE [tempdb] 
GOdrop table [BlogComment]
CREATE TABLE [BlogComment](
[ID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[BlogID] [int] NOT NULL,
[CommentID] [int] NOT NULL,
[Context] [nvarchar](max) NULL,
[CreateTime] [datetime] NOT NULL,
)GOinsert BlogComment values(1, 1, 1, 0, '回复1', '2010-3-21')
insert BlogComment values(2, 2, 1, 0, '回复2', '2010-3-22')
insert BlogComment values(3, 3, 1, 0, '回复3', '2010-3-23')
insert BlogComment values(4, 4, 1, 2, '回复2-评论1', '2010-3-24')
insert BlogComment values(5, 3, 1, 0, '回复4', '2010-3-25')
insert BlogComment values(6, 2, 1, 2, '回复2-评论2', '2010-3-26')select * from BlogComment where BlogID = 1 order by CreateTime asc现在的查询语句的结果是：ID    UserID    BlogID  CommentID   Context          CreateTime
1 1 1 0 回复1 2010-03-21 00:00:00.000
2 2 1 0 回复2 2010-03-22 00:00:00.000
3 3 1 0 回复3 2010-03-23 00:00:00.000
4 4 1 2 回复2-评论1 2010-03-24 00:00:00.000
5 3 1 0 回复4 2010-03-25 00:00:00.000
6 2 1 2 回复2-评论2 2010-03-26 00:00:00.000
而我需要的返回是,对“回复2”的两条“评论”应该紧跟着“回复2”后面返回：
ID    UserID    BlogID  CommentID   Context          CreateTime
1 1 1 0 回复1 2010-03-21 00:00:00.000
2 2 1 0 回复2 2010-03-22 00:00:00.000
4 4 1 2 回复2-评论1 2010-03-24 00:00:00.000
6 2 1 2 回复2-评论2 2010-03-26 00:00:00.000
3 3 1 0 回复3 2010-03-23 00:00:00.000
5 3 1 0 回复4 2010-03-25 00:00:00.000

Answer 1

select * from BlogComment where BlogID = 1 order by Context,CreateTime ascID          UserID      BlogID      CommentID   Context                                                                                                                                                                                                                                                          CreateTime
----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1           1           1           0           回复1                                                                                                                                                                                                                                                              2010-03-21 00:00:00.000
2           2           1           0           回复2                                                                                                                                                                                                                                                              2010-03-22 00:00:00.000
4           4           1           2           回复2-评论1                                                                                                                                                                                                                                                          2010-03-24 00:00:00.000
6           2           1           2           回复2-评论2                                                                                                                                                                                                                                                          2010-03-26 00:00:00.000
3           3           1           0           回复3                                                                                                                                                                                                                                                              2010-03-23 00:00:00.000
5           3           1           0           回复4                                                                                                                                                                                                                                                              2010-03-25 00:00:00.000(6 行受影响)

Answer 2

select *
from BlogComment
order by case when CommentID!=0 then CommentID else id end

Answer 3

CREATE TABLE [BlogComment](
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [BlogID] [int] NOT NULL,
    [CommentID] [int] NOT NULL,
    [Context] [nvarchar](max) NULL,
    [CreateTime] [datetime] NOT NULL,
)GOinsert BlogComment values(1, 1, 1, 0, '回复1', '2010-3-21')
insert BlogComment values(2, 2, 1, 0, '回复2', '2010-3-22')
insert BlogComment values(3, 3, 1, 0, '回复3', '2010-3-23')
insert BlogComment values(4, 4, 1, 2, '回复2-评论1', '2010-3-24')
insert BlogComment values(5, 3, 1, 0, '回复4', '2010-3-25')
insert BlogComment values(6, 2, 1, 2, '回复2-评论2', '2010-3-26')select * from BlogComment where BlogID = 1 order by  Context,CreateTime,len(Context) ascdrop table BlogComment
/*ID          UserID      BlogID      CommentID   Context                                                                                                                                                                                                                                                          CreateTime
----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1           1           1           0           回复1                                                                                                                                                                                                                                                              2010-03-21 00:00:00.000
2           2           1           0           回复2                                                                                                                                                                                                                                                              2010-03-22 00:00:00.000
4           4           1           2           回复2-评论1                                                                                                                                                                                                                                                          2010-03-24 00:00:00.000
6           2           1           2           回复2-评论2                                                                                                                                                                                                                                                          2010-03-26 00:00:00.000
3           3           1           0           回复3                                                                                                                                                                                                                                                              2010-03-23 00:00:00.000
5           3           1           0           回复4                                                                                                                                                                                                                                                              2010-03-25 00:00:00.000(6 行受影响)
*/

Answer 4

select * from BlogComment where BlogID = 1 order by Context /*
ID          UserID      BlogID      CommentID   Context              CreateTime                                             
----------- ----------- ----------- ----------- -------------------- ------------------------------------------------------ 
1           1           1           0           回复1                  2010-03-21 00:00:00.000
2           2           1           0           回复2                  2010-03-22 00:00:00.000
4           4           1           2           回复2-评论1              2010-03-24 00:00:00.000
6           2           1           2           回复2-评论2              2010-03-26 00:00:00.000
3           3           1           0           回复3                  2010-03-23 00:00:00.000
5           3           1           0           回复4                  2010-03-25 00:00:00.000（所影响的行数为 6 行）*/

Answer 5

select * from BlogComment where BlogID = 1 order by Context这样好像就可以

Answer 6

CREATE TABLE [BlogComment](
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [BlogID] [int] NOT NULL,
    [CommentID] [int] NOT NULL,
    [Context] [nvarchar](max) NULL,
    [CreateTime] [datetime] NOT NULL,
)GOinsert BlogComment values(1, 1, 1, 0, '回复1', '2010-3-21')
insert BlogComment values(2, 2, 1, 0, '回复2', '2010-3-22')
insert BlogComment values(3, 3, 1, 0, '回复3', '2010-3-23')
insert BlogComment values(4, 4, 1, 2, '回复2-评论1', '2010-3-24')
insert BlogComment values(5, 3, 1, 0, '回复4', '2010-3-25')
insert BlogComment values(6, 2, 1, 2, '回复2-评论2', '2010-3-26')
select * from BlogComment where BlogID = 1 order by ContextID          UserID      BlogID      CommentID   Context                                                                                                                                                                                                                                                          CreateTime
----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1           1           1           0           回复1                                                                                                                                                                                                                                                              2010-03-21 00:00:00.000
2           2           1           0           回复2                                                                                                                                                                                                                                                              2010-03-22 00:00:00.000
4           4           1           2           回复2-评论1                                                                                                                                                                                                                                                          2010-03-24 00:00:00.000
6           2           1           2           回复2-评论2                                                                                                                                                                                                                                                          2010-03-26 00:00:00.000
3           3           1           0           回复3                                                                                                                                                                                                                                                              2010-03-23 00:00:00.000
5           3           1           0           回复4                                                                                                                                                                                                                                                              2010-03-25 00:00:00.000(6 行受影响)

Answer 7

好吧，我承认我提问有些地方没有说清楚，所以，这次只有 josy 的回复才是我要的答案。