问题描述
我在我的应用程序中使用博客评论,我将每个评论行插入一个表中,因此如果他们中的任何一个回复了点击 commentid 的特定评论,我将作为 replyid<插入/code> 在新行中.
I am using blog comments in my application where I insert every comment row in one table so if any of them reply on a particular comment that on clicked commentid I am inserting as replyid in new row.
以下是屏幕截图:
在这里你可以看到 commentid 24 和 26 有 replycommentid 23.我需要一个查询来在 23 之后显示 24 和 26.因为 23 是 24 和 26 的父级.
Here you can see for commentid 24 and 26 is having replycommentid 23. I need a query to show 24 and 26 just after 23. Because 23 is the parent of 24 and 26.
这是表格布局和示例数据的设置脚本:
Here is the setup script for table layout and sample data:
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
欢迎所有建议!
我还在下面添加了三个插入查询:
I added three insert query also in below:
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
Valex, please insert this query in the table which is "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
- 回复
- 回复
- 回复
我现在正在编辑我的问题.请检查下面的屏幕截图:
I am editing my question now. Please check the screen shot below:
我使用了下面的查询:
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
以下是显示评论的图片:
Below is the image for showing comment:
以下是预期的结构:
- 维卡什评论
- Sid 在 vi??kash 评论中得到回复.
- Megha 对 vikash 发表了评论,因此 megha 评论比父母 Vikash 下的 Sid 多.
- QE 回复了 Megha 的评论,所以他应该比 Sid 高,但它排在最后一排.
推荐答案
您应该使用递归 CTE.这是改编来自您后来的问题
You should use recursive CTE. Here is the adaptation from your later question
WITH CTE AS ( SELECT CommentID, CommentUserName, CommentText,CommentDate,ReplyCommentID, CommentID as ThreadID, CAST(CommentID as varchar(MAX)) as PathStr FROM Blog_CommentDetails as T WHERE ReplyCommentID IS NULL UNION ALL SELECT T.CommentID, T.CommentUserName, t.CommentText,T.CommentDate, T.ReplyCommentID, CTE.ThreadID, PathStr+'-' +CAST(T.ReplyCommentID as varchar(MAX)) as PathStr FROM Blog_CommentDetails as T JOIN CTE ON T.ReplyCommentID = CTE.CommentID WHERE T.ReplyCommentID IS NOT NULL ) SELECT * FROM CTE ORDER BY ThreadID,PathStr,CommentID, CommentDate desc
结果:
╔═══════════╦═════════════════╦═══════════════════════╦═════════════════════════╦════════════════╦══════════╦═════════╗ ║ CommentID ║ CommentUserName ║ CommentText ║ CommentDate ║ ReplyCommentID ║ ThreadID ║ PathStr ║ ╠═══════════╬═════════════════╬═══════════════════════╬═════════════════════════╬════════════════╬══════════╬═════════╣ ║ 22 ║ Vikash ║ This is main comment ║ 2016-11-12 17:36:25.637 ║ NULL ║ 22 ║ 22 ║ ║ 23 ║ Megha k ║ This is reply comment ║ 2016-11-12 17:39:04.250 ║ NULL ║ 23 ║ 23 ║ ║ 24 ║ Siddappa H ║ This is reply text. ║ 2016-11-12 17:39:58.847 ║ 23 ║ 23 ║ 23-23 ║ ║ 26 ║ Vikash ║ This is reply text ║ 2016-11-12 17:41:44.673 ║ 23 ║ 23 ║ 23-23 ║ ║ 25 ║ Suresh P ║ This is reply comment ║ 2016-11-12 17:40:44.470 ║ NULL ║ 25 ║ 25 ║ ╚═══════════╩═════════════════╩═══════════════════════╩═════════════════════════╩════════════════╩══════════╩═════════╝