本文介紹了如何使用 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 在 vi??kash 評論中得到回復.
- 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模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！