SQL两列求和及查询优化：如何移除子查询改用连接实现同等效果

改写后SQL #

SELECT
    IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
    COALESCE(child_cnt.ChildContentcount, 0) AS ChildContentcount,
    IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) + COALESCE(child_cnt.ChildContentcount, 0) AS sumdata
FROM 
    comments c2 
LEFT JOIN (
    SELECT 
        parentcommentid,
        COUNT(content) AS ChildContentcount
    FROM comments
    WHERE isdeleted = 0 AND approved = 1
    GROUP BY parentcommentid
) child_cnt ON child_cnt.parentcommentid = c2.id
WHERE  
    c2.discussionid = '257943' 
    AND c2.parentcommentid IS NULL
ORDER BY 
    c2.pinned DESC,
    c2.createddate 

逻辑说明 #

• 移除嵌套子查询：先通过预聚合查询统计所有符合「未删除、已审核」条件的子评论，按父评论ID分组计算数量，再通过LEFT JOIN关联父评论表即可，和原查询效果完全一致。使用COALESCE函数将无子评论时返回的NULL转换为0，保证和原输出逻辑对齐。
• 新增sumdata列：直接将Contentcount和ChildContentcount两列数值相加即可，完全匹配给出的期望输出结果。

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