如何使用SQL查询显示层次行，例如父子关系

Question

如何使用SQL查询显示层次行，例如父子关系

sql-serversql-server-2008t-sql

6

我在我的应用程序中使用博客评论，我将每个评论行插入一个表格中，所以如果他们回复特定的被点击的评论commentid，我会将其作为replyid插入新行。

以下是屏幕截图：

这里你可以看到，commentid为24和26的评论具有 replycommentid23。我需要一条查询来显示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

我正在编辑我的问题。

请查看下面的屏幕截图：

我使用的查询如下：

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之上，但实际上在最后一行。

- Vikash

我正在编辑并添加脚本，用于这个表格。 - Vikash

有人可以回答我的问题吗？我还在等待答案。 - Vikash

4个回答

2

你应该使用递归CTE。这里是从你后来的问题进行了改编的答案。

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      ║
╚═══════════╩═════════════════╩═══════════════════════╩═════════════════════════╩════════════════╩══════════╩═════════╝

- valex

Valex，我在上面的问题中添加了三个新的插入查询，请插入它们。插入后，您会发现三个用户名：维基，比卢和苏雷什，因此在这里，维基是比卢和苏雷什的父级。根据评论日期，苏雷什是最后发表评论的人，因此Rakesh应该比比卢更高，但是使用您的查询，仍然显示比卢排名第一，Rakesh排名第二。 - Vikash

@Vikash 我已经添加了这3个插入（将vicky的ReplyCommentID更改为NULL而不是你的插入中的0）。在相等级别排序中，按照最后一个ORDER BY ThreadID、PathStr、CommentID进行排序。因此，如果billu和suresh处于同一级别，则使用CommentID进行排序，如果您想根据CommentDate对它们进行排序，则在最后一个ORDER BY中将CommentID更改为CommentDate。 - valex

不，Valex、Rakesh和Billu不在同一级别。首先，Billu被评论了，然后是Vicky的回复，最后是Rakesh的评论。根据预期的系统，Rakesh的评论将会在上面，而Billu将会在Vicky下面。你明白我的意思吗？如果有任何疑问，请问我。 - Vikash

@vikash 我的意思是它们都在同一个Vicky的帖子下面，所以它们有相同的计算路径，你可以按照我之前的评论所解释的任意排序它们。 - valex

Valex，我尝试了这个查询，只需要添加commentdate按desc排序的注释，这样最后一条评论的人就会坐在父级下面。你的其余代码都很好。 - Vikash

显示剩余2条评论

2

你可以像下面显示的代码片段一样进行排序：

select coalesce(replycommentid, commentid) as sequence, ...
order by sequence, ...

父行将使用自己的ID作为序列。

- jim31415

0

如果我理解你的需求，你想要每个评论都跟随它的回复，然后才是下一个（非回复）评论。我认为你需要的只是这样：

select * from Blog_CommentDetails
order by coalesce(ReplyCommentID, commentID), CommentID

- cco

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Cappelletti · Accepted Answer

如果您想获取完整的层次结构或其中一部分，并保持正确的顺序。

编辑-在2008中删除了Concat()

Declare @Top  int = 23 --null             --<<  Sets top of Hier Try 23
Declare @Nest varchar(25) ='|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(1000+Row_Number() over (Order by CommentID) as varchar(500))
            ,CommentID
            ,ReplyCommentID
            ,Lvl=1
      From   [dbo].[Blog_CommentDetails]
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ReplyCommentID,-1) else CommentID end
      Union  All
      Select Seq  = cast(p.Seq+'.'+cast(1000+Row_Number() over (Order by r.CommentID) as varchar(25)) as varchar(500))
            ,r.CommentID
            ,r.ReplyCommentID
            ,p.Lvl+1
      From   [dbo].[Blog_CommentDetails] r 
      Join   cteP p on r.ReplyCommentID = p.CommentID)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
     ,cteR2 as (Select A.Seq,A.CommentID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.CommentID )
Select A.R1  
      ,B.R2
      ,A.CommentID
      ,A.ReplyCommentID
      ,A.Lvl
      ,CommentText = Replicate(@Nest,A.Lvl-1) + C.CommentText
      -- Include any other fields from [dbo].[Blog_CommentDetails] alias C
 From cteR1 A 
 Join cteR2 B on A.CommentID=B.CommentID
 Join [dbo].[Blog_CommentDetails] C on A.CommentID=C.CommentID
 Order By A.R1

返回

如果@Top设置为23（例如），则返回值将为

我应该补充说明，cteR2并非必需，但它能指示范围和/或父级或叶子级别。