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

时间：2023-02-24

本文介绍了如何使用 SQL 查询显示像父子项一样的层次结构行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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


                        这篇关于如何使用 SQL 查询显示像父子项一样的层次结构行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！



上一篇：SQL Server 根据不同的标志计算累积总和/条件运行 
下一篇：什么是 SQL“伪列"? 

 
相关文章
将每个子标记转换为具有多个分隔符的单列-SQLConverting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(将每个子标记转换为具有多个分隔符的单列-SQ
如何从多个表创建视图?How can I create a view from more than one table?(如何从多个表创建视图?)
根据前一行内的计算值创建计算值Create calculated value based on calculated value inside previous row(根据前一行内的计算值创建计算值)
如何将表格的前两列堆叠成一列，但也仅将第三How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何将表格的前两列堆
递归 t-sql 查询Recursive t-sql query(递归 t-sql 查询)
将月份名称转换为日期/月份编号(问题和答案的组Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(将月份名称转换为日期/月份编号(问题和答案的组合



最新文章

SQL Server getdate() 转换为类似“2009-12-20"的字符
SQL在删除子表行时锁定父表
T-SQL:从 text 或 ntext 列中选择前 n 个字符
将 TSQL 格式化为一行
T-SQL中SELECT和SET有什么区别
带有两组括号而不是一组括号的默认值
SQL Server 中的第一个星期一
使用 MOVE 确定 DB RESTORE 的 SQL 数据路径
按计数计数的 SQL 组
整理声明的 SQL 变量