如何在COUNT DISTINCT SQL查询中添加SUM分组聚合逻辑

核心思路 #

你需要先按quizzes.id和tags.id分组计算标签得分总和，再基于聚合结果筛选，最后统计符合条件的测验数量，有两种常用实现方式：

• 方案1：通过CTE（公用表表达式）先完成分组聚合和筛选，再统计结果，逻辑清晰可读性高
• 方案2：嵌套子查询完成分组筛选，兼容性更好，支持不支持CTE的旧版本数据库

具体实现代码 #

方案1：CTE写法 #

WITH quiz_tag_score AS (
  SELECT
    quizzes.id AS quiz_id,
    SUM(tag_scores.score) AS total_tag_score
  FROM quizzes
  INNER JOIN sessions ON sessions.id = quizzes.session_id
  INNER JOIN subscriptions ON subscriptions.id = sessions.subscription_id
  LEFT JOIN quiz_answers ON quiz_answers.quiz_id = quizzes.id
  LEFT JOIN answers ON answers.id = quiz_answers.answer_id
  LEFT JOIN tag_scores ON tag_scores.answer_id = answers.id
  LEFT JOIN tags ON tags.id = tag_scores.tag_id
  WHERE subscriptions.state = 'subscribed'
    AND tags.id = 56 -- 提前过滤目标标签，减少不必要的聚合计算
  GROUP BY quizzes.id, tags.id
  HAVING SUM(tag_scores.score) <= 10 -- 基于聚合后的总分筛选
)
SELECT COUNT(quiz_id) AS qualified_quiz_count
FROM quiz_tag_score;

方案2：子查询写法 #

SELECT COUNT(*) AS qualified_quiz_count
FROM (
  SELECT quizzes.id
  FROM quizzes
  INNER JOIN sessions ON sessions.id = quizzes.session_id
  INNER JOIN subscriptions ON subscriptions.id = sessions.subscription_id
  LEFT JOIN quiz_answers ON quiz_answers.quiz_id = quizzes.id
  LEFT JOIN answers ON answers.id = quiz_answers.answer_id
  LEFT JOIN tag_scores ON tag_scores.answer_id = answers.id
  LEFT JOIN tags ON tags.id = tag_scores.tag_id
  WHERE subscriptions.state = 'subscribed'
    AND tags.id = 56
  GROUP BY quizzes.id, tags.id
  HAVING SUM(tag_scores.score) <= 10
) AS valid_quizzes;

注意说明 #

• 原有查询中WHERE条件包含tags.id = 56，已经将tags表的左连接转为内连接效果，不会统计没有关联到该标签的测验
• 如果需要统计没有该标签得分的测验（默认总分按0计算），可以将tags.id = 56的过滤条件移动到tags表的JOIN条件中，同时调整HAVING判断逻辑适配空值情况

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