如何使用 SQL 查询显示像父子项一样的层次结构行 [英] How to show hierarchy row like parent child using SQL query
本文介绍了如何使用 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 查询显示像父子项一样的层次结构行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
