如何使用 SQL 查询显示像父子项一样的层次结构行

2021-09-10 00:00:00 tsql sql-server-2008 sql-server

我在我的应用程序中使用博客评论，我将每个评论行插入一个表中，因此如果他们中的任何一个回复了点击 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 在 vikash 评论中得到回复.
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      ║
╚═══════════╩═════════════════╩═══════════════════════╩═════════════════════════╩════════════════╩══════════╩═════════╝


	
		相关文章