求助：按规则过滤Comment表中Status为Deleted的记录

需求说明 #

• Comment表仅支持一级嵌套：ParentId为NULL时是独立主评论，非NULL时是对主评论的回复
• 需要过滤Status为Deleted的记录，但需遵循以下规则：
  • 被其他用户回复的已删除主评论（如Id=22）必须保留，用于UI显示“Comment removed”以保证回复逻辑通顺
  • 未被回复的已删除主评论/已删除回复（如Id=24、26）需移除，不影响评论线程完整性

修改后的SQL查询 #

WITH RepliedParentComments AS (
    -- 找出所有存在回复的主评论ID
    SELECT DISTINCT ParentId
    FROM Comment
    WHERE ParentId IS NOT NULL
)
SELECT
    c.Id,
    c.ParentId,
    c.ReplyId,
    c.Comment,
    c.[Status],
    u.Id AS UserId,
    u2.Id AS ReplyToUserId
FROM 
    Post p
JOIN 
    Comment c ON p.Id = c.PostId
JOIN 
    [User] u ON c.UserId = u.Id
LEFT OUTER JOIN 
    [User] u2 ON c.ReplyToUserId = u2.Id
WHERE 
    p.Id = 33
    -- 核心过滤条件
    AND (
        -- 状态不是Deleted的记录直接保留
        c.[Status] != 'Deleted'
        -- 已删除的主评论，但存在回复的需要保留
        OR (c.[Status] = 'Deleted' AND c.ParentId IS NULL AND EXISTS (
            SELECT 1 FROM RepliedParentComments rpc WHERE rpc.ParentId = c.Id
        ))
    )
ORDER BY 
    (CASE WHEN c.ParentId IS NULL THEN c.Id ELSE c.ParentId END), c.Id

逻辑解释 #

1. CTE RepliedParentComments：提前筛选出所有有回复的主评论ID，避免重复子查询计算
2. 过滤条件：
   • 保留所有状态非Deleted的评论/回复
   • 仅保留已删除且存在回复的主评论，这类评论需要留在结果中保证回复线程的完整性
   • 自动排除未被回复的已删除主评论，以及所有已删除的回复（一级嵌套下回复不会被其他回复指向，符合规则2的移除要求）

内容的提问来源于stack exchange，提问作者PixelPaul