sql查询语句，来人帮忙看看。

Question

  
  create table [New_CommentInfo]
  (
  Id int identity(1,1) primary key 
      ,[Pageid] int
      ,[Email]nvarchar(50)
      ,[Comment]nvarchar(512)
      ,[Addtime]datetime
      ,[Isdelete]int
  )  insert into [New_CommentInfo] values(1,'[email protected]','1可以的。',GETDATE(),0)
  insert into [New_CommentInfo] values(1,'[email protected]','3可以的。','2013-09-07 14:42:12',0)
  insert into [New_CommentInfo] values(1,'[email protected]','2可以的。','2013-09-09 12:12:12',0)
  create table [New_ReplyInfo](
  [Id] int identity(1,1) primary key 
      ,[Commentid] int 
      ,[Email] nvarchar(50)
      ,[Reply] nvarchar(512)
      ,[Addtime] datetime
      ,[Isdelete] int)    insert into [New_ReplyInfo] values(1,'[email protected]','我也期待[email protected]',GETDATE(),0)  
    insert into [New_ReplyInfo] values(1,'[email protected]','我也期待[email protected]',GETDATE(),0)  
    insert into [New_ReplyInfo] values(2,'[email protected]','我也期待[email protected]',GETDATE(),0)  需要让每条回复都在评论下面显示，并且最新的评论在最前面。
效果应该是这样。麻烦各位帮下忙，谢谢。
1  [email protected] 1可以的。           2013-09-24 16:02:12 0
1  [email protected] 我也期待[email protected] 2013-09-24 16:02:12 0(这是评论的回复)
1  [email protected] 我也期待[email protected] 2013-09-24 16:02:12 0(这是评论的回复)
3  [email protected] 2可以的。           2013-09-09 12:12:12 02  [email protected] 3可以的。           2013-09-07 14:42:12 0
2  [email protected] 我也期待[email protected] 2013-09-24 16:02:12 0(这是评论的回复)sql查询语句

Answer 1

New_CommentInfo 是评论
New_ReplyInfo 是对New_CommentInfo中数据的回复。
我想呈现出来的时候是 
评论1
回复1
评论2
评论3
回复3
评论4这样 不知道大版明白了没

Answer 2

try this,select Id,Email,Comment,Addtime,Isdelete
from
(select Id,Email,Comment,convert(varchar(20),Addtime,120) 'Addtime',
        convert(varchar(20),Addtime,120) 'Addtime2',Isdelete,0 'lv'
   from New_CommentInfo
 union all
 select a.Commentid,a.Email,a.Reply,convert(varchar(20),a.Addtime,120) 'Addtime',
        convert(varchar(20),b.Addtime,120) 'Addtime2',a.Isdelete,1 'lv'
   from New_ReplyInfo a
   inner join New_CommentInfo b on a.Commentid=b.Id
) t order by Addtime2 desc,lv/*
Id          Email            Comment                        Addtime              Isdelete
----------- ---------------- ------------------------------ -------------------- -----------
1           [email protected]      1可以的。                          2013-09-24 16:41:08   0
1           [email protected]      我也期待[email protected]              2013-09-24 16:41:17   0
1           [email protected]      我也期待[email protected]              2013-09-24 16:41:17   0
3           [email protected]      2可以的。                          2013-09-09 12:12:12   0
2           [email protected]      3可以的。                          2013-09-07 14:42:12   0
2           [email protected]      我也期待[email protected]              2013-09-24 16:41:17   0(6 row(s) affected)
*/

Answer 3

可以在其中加入pageid字段么？因为最后需要pageid来做筛选。

Answer 4

可以在其中加入pageid字段么？因为最后需要pageid来做筛选。
--> 可以.select Id,Email,Comment,Addtime,Isdelete,Pageid
from
(select Id,Email,Comment,convert(varchar(20),Addtime,120) 'Addtime',Pageid,
        convert(varchar(20),Addtime,120) 'Addtime2',Isdelete,0 'lv'
   from New_CommentInfo
 union all
 select a.Commentid,a.Email,a.Reply,convert(varchar(20),a.Addtime,120) 'Addtime',b.Pageid,
        convert(varchar(20),b.Addtime,120) 'Addtime2',a.Isdelete,1 'lv'
   from New_ReplyInfo a
   inner join New_CommentInfo b on a.Commentid=b.Id
) t order by Addtime2 desc,lv/*
Id          Email            Comment                        Addtime              Isdelete    Pageid
----------- ---------------- ------------------------------ -------------------- ----------- -----------
1           [email protected]      1可以的。                          2013-09-24 16:41:08  0           1
1           [email protected]      我也期待[email protected]                2013-09-24 16:41:17  0           1
1           [email protected]      我也期待[email protected]                2013-09-24 16:41:17  0           1
3           [email protected]      2可以的。                          2013-09-09 12:12:12  0           1
2           [email protected]      3可以的。                          2013-09-07 14:42:12  0           1
2           [email protected]      我也期待[email protected]                2013-09-24 16:41:17  0           1(6 row(s) affected)
*/

Answer 5

是这个么？？select tt.Pageid, tt.Email, tt.Comment,tt.Addtime
  from (select t.Pageid,
               t.Email,
               t.Comment,
               t.Addtime,
               row_number() over(order by t.Addtime desc) rn
          from New_CommentInfo t
        union all
        select t1.Pageid, t2.Email,t2.Reply,t2.Addtime,t1.rn
          from (select t.Pageid,
               t.Email,
               t.Comment,
               t.Addtime,
               row_number() over(order by t.Addtime desc) rn
          from New_CommentInfo t) t1,
               New_ReplyInfo t2
         where t1.Pageid = t2.Commentid) tt
 order by rn, Addtime desc

Answer 6

select Id,Email,type,Comment,Addtime,Isdelete,Pageid
from
(select Id,Email,Comment,convert(varchar(20),Addtime,120) 'Addtime',Pageid,'评论' 'type',
        convert(varchar(20),Addtime,120) 'Addtime2',Isdelete,0 'lv'
   from New_CommentInfo
 union all
 select a.Commentid,a.Email,a.Reply,convert(varchar(20),a.Addtime,120) 'Addtime',b.Pageid,'回复' 'type',
        convert(varchar(20),b.Addtime,120) 'Addtime2',a.Isdelete,1 'lv'
   from New_ReplyInfo a
   inner join New_CommentInfo b on a.Commentid=b.Id
) t order by Addtime2 desc,lv/*
Id          Email            type Comment                        Addtime              Isdelete    Pageid
----------- ---------------- ---- ------------------------------ -------------------- ----------- -----------
1           [email protected]      评论   1可以的。                          2013-09-24 16:41:08  0           1
1           [email protected]      回复   我也期待[email protected]                2013-09-24 16:41:17  0           1
1           [email protected]      回复   我也期待[email protected]                2013-09-24 16:41:17  0           1
3           [email protected]      评论   2可以的。                          2013-09-09 12:12:12  0           1
2           [email protected]      评论   3可以的。                          2013-09-07 14:42:12  0           1
2           [email protected]      回复   我也期待[email protected]                2013-09-24 16:41:17  0           1(6 row(s) affected)
*/