如何更高效地编写这个查询？

Question

在我的个人主页上，用户可以评论和回复评论（类似Facebook的1级评论）。由于一个主页可能会有成千上万条评论，因此我正在实现一些分页功能。尽管分页已经生效，但是由于存在子评论，简单的top n查询会破坏对话结构。因此，我只想对父评论进行分页，而不是对子评论进行分页。

表“comments”包含以下内容：

- commentID
- commentText
- parentCommentID
- commentOnUserID

这里的问题在于我只想对父级评论（parentCommentID = 0）进行分页。所以我编写了以下查询：

select * from Comments c
where c.parentCommentID = 0 
and c.commentOnUserID = 65939 

(因为它不相关，所以我省略了实际的分页查询)

但我也想加载所有这些评论的子评论，一个子评论也是一条评论，但其parentCommentID = 某个commentID：

select * from comments c
where c.parentCommentID in ( * get the commentId's from the previous query)
and c.commentOnUserID = 65939

有没有一种有效的方法可以在一个查询中高效地将这两个内容组合起来？

- Elger Mensonides

Answer 1

declare @T table(commentID int,
                 commentText varchar(max),
                 parentCommentID int,
                 commentOnUserID int)

insert into @T values
(1, 'Comment 1', 0, 1),
(2, 'Comment 2', 0, 1),
(3, 'Comment 3', 0, 1),
(4, 'Comment 4 sub 1', 1, 1),
(5, 'Comment 5 sub 1', 1, 1),
(6, 'Comment 6 sub 1', 1, 1),
(7, 'Comment 1 sub 2', 2, 1),
(8, 'Comment 1 sub 2', 2, 1),
(9, 'Comment 1 sub 3', 3, 1)

declare @UserID int = 1

;with cte as
(
  select
    T.commentID,
    T.CommentText,
    row_number() over(order by commentID) as rn
  from @T as T
  where
    T.parentCommentID = 0 and
    T.commentOnUserID = @UserID
  union all
  select   
    T.commentID,
    T.CommentText,
    C.rn
  from @T as T
    inner join cte as C
      on T.parentCommentID = C.commentID
)
select *
from cte
where rn between 1 and 2 -- use rn for pagination
order by rn, commentID

结果

commentID   parentCommentID CommentText          rn
----------- --------------- -------------------- --------------------
1           0               Comment 1            1
4           1               Comment 4 sub 1      1
5           1               Comment 5 sub 1      1
6           1               Comment 6 sub 1      1
2           0               Comment 2            2
7           2               Comment 1 sub 2      2
8           2               Comment 1 sub 2      2

- Mikael Eriksson

Answer 2

像这样：

WITH
    ParentComment AS (
        SELECT * from Comments c
        WHERE c.parentCommentID = 0 
        AND c.commentOnUserID = 65939 
    )
SELECT * 
FROM Comments c
WHERE c.commentOnUserID = 65939
AND (
    c.CommentID IN (SELECT CommentID FROM ParentComment)
    OR c.ParentCommentID IN (SELECT CommentID FROM ParentComment)
)

尚未在SQL Server中测试语法，但这应该是一般想法。

- Isaac Truett

Answer 3

我会用类似以下的方式来实现这个：

SELECT p.*, c.* 
FROM comment c LEFT JOIN comment p ON (c.parentCommentID = p.commentID)
WHERE p.parentCommentID = 0
AND p.commentOnUserID = 65939

我相信有一种好的方法可以将父级包含在子级结果中，但至少在MySQL中，在连接条件中放置OR会导致性能问题。

- nzroller