How to show hierarchy row like parent child using

2019-06-20 06:12发布

站内文章 / SQL-Server
3 0 0

问题:

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.

Below is the Screen shot:

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

All suggestions are welcome!

I added three insert query also in below:

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

Test

    Reply
', 0, 1, N'vicky@gmail.com', NULL, NULL,0) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (61, N'Billu', N'This is comment test', 0, 1, CAST(N'2016-12-02 12:35:40.220' AS DateTime), N'BilluDec2,2016 12:34PM

This is comment test

    Reply
', 0, 1, N'billu@gmail.com', NULL, 58) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (62, N'Rakesh', N'This is rakesh test', 0, 1, CAST(N'2016-12-02 12:37:42.133' AS DateTime), N'RakeshDec2,2016 12:36PM

This is rakesh test

    Reply
', 0, 1, N'rakesh@gmail.com', NULL, 58)

I am editing my question now. Please check the screen shot below:

I used the query which is 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:

Below is the expected structure:

  1. Vikash comment
  2. Sid is replied on vikash comment.
  3. Megha is commented on vikash so megha comment is up than Sid under parent Vikash.
  4. QE replied on Megha comment so he should be up than Sid but it is in last row.

回答1:

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

Result:

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


回答2:

If you want either the full hierarchy or even a portion of it, with the proper sequence

EDIT - Removed Concat() for 2008

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

Returns

If @Top was set to 23 (for example), the return would be

I should add, cteR2 is not necessary, but it does indicate scope and/or Parent or Leaf level.



回答3:

You could order it like the smippet shown below:

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

A parent row will use it's own id as the sequence.



回答4:

If I understand what you're looking for, you want each comment followed by its replies before any (non-reply) following comment. I think what you want is simply this:

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


标签： sql-server sql-server-2008 tsql
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~