22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [ExpertReply](
[ID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[BlogID] [int] NOT NULL,
[CommentID] [int] NOT NULL,
[Context] [nvarchar](max) NULL,
[CreateTime] [datetime] NOT NULL,
)
insert ExpertReply values(1, 1, 1, 0, '回复1', GETDATE())
insert ExpertReply values(2, 2, 1, 0, '回复2', GETDATE())
insert ExpertReply values(3, 3, 1, 0, '回复3', GETDATE())
insert ExpertReply values(4, 4, 1, 2, '评论1', GETDATE())
insert ExpertReply values(5, 3, 1, 3, '评论3', GETDATE())
insert ExpertReply values(6, 2, 1, 2, '评论2', GETDATE())
insert ExpertReply values(7, 3, 1, 3, '评论4', GETDATE())
--CREATE TABLE [ExpertReply](
-- [ID] [int] NOT NULL,
-- [UserID] [int] NOT NULL,
-- [BlogID] [int] NOT NULL,
-- [CommentID] [int] NOT NULL,
-- [Context] [nvarchar](max) NULL,
-- [CreateTime] [datetime] NOT NULL,
--)
--insert ExpertReply values(1, 1, 1, 0, '回复1', GETDATE())
--insert ExpertReply values(2, 2, 1, 0, '回复2', GETDATE())
--insert ExpertReply values(3, 3, 1, 0, '回复3', GETDATE())
--insert ExpertReply values(4, 4, 1, 2, '评论1', GETDATE())
--insert ExpertReply values(5, 3, 1, 3, '评论3', GETDATE())
--insert ExpertReply values(6, 2, 1, 2, '评论2', GETDATE())
----insert ExpertReply values(7, 3, 1, 3, '评论4', GETDATE())
;WITH cte AS (
SELECT id,CommentID,context,id AS parentid
FROM ExpertReply
WHERE CommentID=0
UNION ALL
SELECT a.id,a.commentid,a.context,b.id AS parentid
FROM ExpertReply a INNER JOIN cte b ON a.CommentID=b.id
)
SELECT id,CommentID,context FROM cte
ORDER BY parentid
/*
id CommentID context
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 回复1
2 0 回复2
4 2 评论1
6 2 评论2
3 0 回复3
5 3 评论3
7 3 评论4
*/
with tb as(select id,commentid=id,context from [ExpertReply] where commentid=0
union all
select a.id,tb.commentid,a.context from tb,[ExpertReply] a where tb.id=a.commentid
)
select context from tb
order by commentid,id
这样OK吗?
with tb as
(
select id,userid,blogid,commentid as old_commentid,context,createtime,commentid=id
from [ExpertReply] where commentid=0
union all
select a.id,a.userid,a.BlogID,a.CommentID,a.context,a.CreateTime,tb.commentid
from tb,[ExpertReply] a where tb.id=a.commentid
)
select ID,UserID,BlogID,old_commentid commetnid,Context,CreateTime,commentid as orderby
from tb
order by commentid,id
这种效果?
--CREATE TABLE [ExpertReply](
-- [ID] [int] NOT NULL,
-- [UserID] [int] NOT NULL,
-- [BlogID] [int] NOT NULL,
-- [CommentID] [int] NOT NULL,
-- [Context] [nvarchar](max) NULL,
-- [CreateTime] [datetime] NOT NULL,
--)
--insert ExpertReply values(1, 1, 1, 0, '回复1', GETDATE())
--insert ExpertReply values(2, 2, 1, 0, '回复2', GETDATE())
--insert ExpertReply values(3, 3, 1, 0, '回复3', GETDATE())
--insert ExpertReply values(4, 4, 1, 2, '评论1', GETDATE())
--insert ExpertReply values(5, 3, 1, 3, '评论3', GETDATE())
--insert ExpertReply values(6, 2, 1, 2, '评论2', GETDATE())
----insert ExpertReply values(7, 3, 1, 3, '评论4', GETDATE())
;WITH cte AS (
SELECT [ID] , [UserID] , [BlogID] , [CommentID] , [Context], [CreateTime],id AS parentid
FROM ExpertReply
WHERE CommentID=0
UNION ALL
SELECT a.id,a.[UserID],a.blogid,a.commentid,a.context,a.createtime,b.id AS parentid
FROM ExpertReply a INNER JOIN cte b ON a.CommentID=b.id
)
SELECT [ID] , [UserID] , [BlogID] , [CommentID] , [Context], [CreateTime] FROM cte
ORDER BY parentid
/*
ID UserID BlogID CommentID Context CreateTime
----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1 1 1 0 回复1 2013-05-14 11:56:16.150
2 2 1 0 回复2 2013-05-14 11:56:16.150
4 4 1 2 评论1 2013-05-14 11:56:16.150
6 2 1 2 评论2 2013-05-14 11:56:16.150
3 3 1 0 回复3 2013-05-14 11:56:16.150
5 3 1 3 评论3 2013-05-14 11:56:16.150
7 3 1 3 评论4
*/