我在我的应用程序中使用博客评论,我将每个评论行插入一个表格中,所以如果他们回复特定的被点击的评论
以下是屏幕截图: 这里你可以看到,
这是表格布局和样例数据的设置脚本:
commentid
,我会将其作为replyid
插入新行。以下是屏幕截图: 这里你可以看到,
commentid
为24和26的评论具有 replycommentid
23。我需要一条查询来显示23之后紧跟着的24和26。因为23是24和26的父级。这是表格布局和样例数据的设置脚本:
USE [myDB]
GO
/****** Object: Table [dbo].[Blog_CommentDetails] Script Date: 11/12/2016 6:36:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_CommentDetails](
[CommentID] [int] IDENTITY(1,1) NOT NULL,
[CommentUserName] [nvarchar](200) NOT NULL,
[CommentText] [nvarchar](max) NULL,
[CommentApprovedByUserID] [int] NULL,
[CommentPostDocumentID] [int] NOT NULL,
[CommentDate] [datetime] NULL DEFAULT (getdate()),
[HtmlComment] [nvarchar](max) NULL,
[CommentIsSpam] [bit] NULL CONSTRAINT [DEFAULT_Blog_MainComment_CommentIsSpam] DEFAULT ((0)),
[CommentIsApproved] [bit] NULL CONSTRAINT [DEFAULT_Blog_MainComment_CommentIsApproved] DEFAULT ((0)),
[CommentEmail] [nvarchar](250) NULL,
[CommentInfo] [nvarchar](max) NULL,
[ReplyCommentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Blog_CommentDetails] ON
GO
INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (22, N'Vikash', N'This is main comment', NULL, 1, CAST(N'2016-11-12 17:36:25.637' AS DateTime), N'<div class="main-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pCommentorName"><span id="sCommentorName" class="blogcommentname">Vikash</span><span id="sBlogPostedDate">Nov12,2016 5:35PM</span></p><p class="comment-detail" id="pBlogCommentDetails">This is main comment</p><div class="comment-reply"><ul><li><img src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnComment" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N'vikash.kr@sonata-software.com', NULL, NULL)
GO
INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (23, N'Megha k', N'This is reply comment', NULL, 1, CAST(N'2016-11-12 17:39:04.250' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Megha k</span><span id="sBlogReplyCommentDate">Nov12,2016 5:38PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply comment</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N'megha.k@sonata-software.com', NULL, NULL)
GO
INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (24, N'Siddappa H', N'This is reply text.', NULL, 1, CAST(N'2016-11-12 17:39:58.847' AS DateTime), N'<div class="main-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pCommentorName"><span id="sCommentorName" class="blogcommentname">Siddappa H</span><span id="sBlogPostedDate">Nov12,2016 5:39PM</span></p><p class="comment-detail" id="pBlogCommentDetails">This is reply text.</p><div class="comment-reply"><ul><li><img src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnComment" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N'siddappa.h@sonata-software.com', NULL, 23)
GO
INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (25, N'Suresh P', N'This is reply comment', NULL, 1, CAST(N'2016-11-12 17:40:44.470' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Suresh P</span><span id="sBlogReplyCommentDate">Nov12,2016 5:40PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply comment</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N'suresh.p@sonata-software.com', NULL, NULL)
GO
INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (26, N'Vikash', N'This is reply text', NULL, 1, CAST(N'2016-11-12 17:41:44.673' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Vikash</span><span id="sBlogReplyCommentDate">Nov12,2016 5:40PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply text</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N'vikash.kr@sonata-software.com', NULL, 23)
GO
SET IDENTITY_INSERT [dbo].[Blog_CommentDetails] OFF
GO
欢迎提出所有建议!
我在下面还添加了三个插入查询:
Valex,请将此查询插入到表中,该表为“INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID], [IsRejected]) VALUES (58, N'Vicky', N'Test', 0, 1, CAST(N'2016-12-02 11:51:07.270' AS DateTime), N'VickyDec2,2016 11:47AM
- 回复
- 回复
- 回复
我正在编辑我的问题。
请查看下面的屏幕截图:![TableStructure](https://istack.dev59.com/5rmN8.webp)
我使用的查询如下:
WITH CTE AS ( SELECT CommentID ,
CommentPostDocumentID ,
CommentIsApproved,
CommentDate ,
ReplyCommentID ,
CommentUserName,
CommentID AS ThreadID ,
CAST( CommentID AS VARCHAR( MAX ) ) AS PathStr
FROM Blog_CommentDetails AS T WITH(NOLOCK)
WHERE ReplyCommentID IS NULL
UNION ALL
SELECT T.CommentID ,
t.CommentPostDocumentID ,
t.CommentIsApproved,
T.CommentDate ,
T.ReplyCommentID ,
T.CommentUserName,
CTE.ThreadID ,
PathStr + '-'+ CAST( T.ReplyCommentID AS VARCHAR( MAX ) ) AS PathStr
FROM Blog_CommentDetails AS T WITH(NOLOCK)
JOIN CTE
ON T.ReplyCommentID = CTE.CommentID
WHERE T.ReplyCommentID IS NOT NULL )
SELECT *
FROM CTE
WHERE CommentPostDocumentID = 18 AND CommentIsApproved=1
ORDER BY ThreadID ,
PathStr ,
CommentDate DESC
以下是预期结构:
- Vikash的评论
- Sid回复了Vikash的评论。
- Megha评论了Vikash,因此Megha的评论位于Sid之上,作为父级Vikash的下方。
- QE回复了Megha的评论,所以他应该在Sid之上,但实际上在最后一行。