如何在同一条SQL查询中同时获取参与者的单轮成绩与总成绩？

你遇到的WITH ROLLUP生成重复行的问题，是因为你按「参与者ID+轮次ID」两个字段分组，ROLLUP会依次对每个分组维度生成汇总，包括“每个参与者的所有轮次汇总”和“整个查询的全局汇总”，所以才会出现多余的行。下面给你两种可行的解决方案，不用拆分查询就能同时拿到单轮成绩和总成绩：

方案一：用窗口函数直接计算总成绩（推荐） #

窗口函数可以在不改变原有分组逻辑的前提下，额外计算每个参与者的全局统计值，这样每一行既保留单轮的成绩，又能看到该参与者的总成绩。修改后的查询如下：

SELECT 
  quiz_participants.id,
  MAX(quiz_participants.name) as name,
  quiz_round_participant_answers.round_id,
  -- 单轮成绩统计
  TIMESTAMPDIFF(MICROSECOND, MIN(qrp.started_at), MAX(qrpa.created_at)) AS round_time_taken,
  COUNT(qrpa.id) as round_answer_count,
  COUNT(a.is_correct) as round_correct_count,
  -- 参与者累计总成绩（窗口函数计算）
  SUM(TIMESTAMPDIFF(MICROSECOND, MIN(qrp.started_at), MAX(qrpa.created_at))) OVER (PARTITION BY quiz_participants.id) AS total_time_taken,
  SUM(COUNT(qrpa.id)) OVER (PARTITION BY quiz_participants.id) AS total_answer_count,
  SUM(COUNT(a.is_correct)) OVER (PARTITION BY quiz_participants.id) AS total_correct_count
FROM quiz_round_participant_answers qrpa
INNER JOIN quiz_participants qp ON qp.id = qrpa.participant_id
INNER JOIN quiz_round_participants qrp ON qrp.participant_id = qp.id AND qrp.round_id = qrpa.round_id -- 新增轮次关联，避免数据交叉重复
INNER JOIN quizzes q ON q.id = qp.quiz_id
LEFT JOIN answers a ON a.id = qrpa.answer_id AND a.is_correct = 1
WHERE q.id = X
GROUP BY qp.id, qrpa.round_id
ORDER BY round_correct_count DESC, round_time_taken ASC;

关键说明： #

• 给表加了简短别名，让SQL更易读；
• 窗口函数OVER (PARTITION BY qp.id)会对每个参与者单独计算累计值，每一行都能直接对比单轮和总成绩；
• 修正了quiz_round_participants的关联条件——原来没关联round_id，会导致一个参与者的多轮记录交叉关联，统计的started_at会出错，现在加上轮次匹配后数据更准确。

方案二：修正WITH ROLLUP的用法，保留需要的汇总行 #

如果你更习惯用ROLLUP，可以通过过滤和标记来只保留每个参与者的汇总行，避免多余的全局汇总：

SELECT 
  qp.id,
  MAX(qp.name) as name,
  CASE WHEN qrpa.round_id IS NULL THEN 'Total' ELSE CAST(qrpa.round_id AS CHAR) END AS round_id,
  TIMESTAMPDIFF(MICROSECOND, MIN(qrp.started_at), MAX(qrpa.created_at)) AS time_taken,
  COUNT(qrpa.id) as answer_count,
  COUNT(a.is_correct) as correct_count
FROM quiz_round_participant_answers qrpa
INNER JOIN quiz_participants qp ON qp.id = qrpa.participant_id
INNER JOIN quiz_round_participants qrp ON qrp.participant_id = qp.id AND qrp.round_id = qrpa.round_id
INNER JOIN quizzes q ON q.id = qp.quiz_id
LEFT JOIN answers a ON a.id = qrpa.answer_id AND a.is_correct = 1
WHERE q.id = X
GROUP BY qp.id, qrpa.round_id WITH ROLLUP
HAVING qp.id IS NOT NULL -- 过滤掉所有参与者的全局汇总行
ORDER BY 
  CASE WHEN qrpa.round_id IS NULL THEN 1 ELSE 0 END, -- 让单轮行排在汇总行前面
  correct_count DESC, 
  time_taken ASC;

关键说明： #

• HAVING qp.id IS NOT NULL会去掉ROLLUP生成的全局汇总行，只保留每个参与者的单轮行和该参与者的汇总行；
• 用CASE把汇总行的round_id显示为Total，方便区分单轮和累计成绩；
• 排序时让单轮成绩先显示，汇总行跟在对应参与者的单轮记录后面。

两种方案里，窗口函数的方式更灵活，不需要处理额外的汇总行，还能直接在每一行对比单轮和总成绩，推荐你优先试试第一种。

备注：内容来源于stack exchange，提问作者Shaun