mysql - 找到聚合的最高值并将其存储在新列中

Question

我有一个查询来汇总一些表中的答案，现在想找出哪一个拥有最多的答案。这是我的查询:

SELECT Sum(surveys_answers.answer) AS Commitments
FROM   surveys 
       LEFT JOIN surveys_answers 
              ON surveys_answers.surveys_id_fk = surveys.surveys_id 
       LEFT JOIN surveys_times 
              ON surveys_answers.answer_time_id = surveys_times.times_id 
WHERE  surveys.surveys_id = 5132 
       AND surveys_answers.answer = 1 
GROUP  BY times_id 
ORDER  BY times_id 

由于我只需要 promise 的数量，这是我选择的唯一列。但我还需要确定哪一行具有最多的 promise ，并希望添加一个 bool 类型的新列“Highest”，以包含具有最高值的行的 true。

我尝试使用 Max(Commitments) AS Highest，但它不起作用。这可以在不创建子查询的情况下实现吗？

目前我得到以下结果:

+-------------+
| Commitments |
+-------------+
| 4           |
+-------------+
| 7           |
+-------------+
| 2           |
+-------------+
| 13          |
+-------------+
| 8           |
+-------------+

我想要这样的结果:

+-------------+---------+
| Commitments | Highest |
+-------------+---------+
| 4           | false   |
+-------------+---------+
| 7           | false   |
+-------------+---------+
| 2           | false   |
+-------------+---------+
| 13          | true    |
+-------------+---------+
| 8           | false   |
+-------------+---------+

提前致谢! :)

Answer 1

您也许可以在此处使用 session 变量技巧。定义一个行号 session 变量，等于1时为最高，否则为最高:

SET @rn = 0;

SELECT
    t.Commitments,
    CASE WHEN rn = 1 THEN 'true' ELSE 'false' END AS Highest
FROM
(
    SELECT
        t.times_id,
        t.Commitments,
        @rn:=@rn+1 AS rn
    FROM
    (
        SELECT
            times_id,
            SUM(surveys_answers.answer) AS Commitments
        FROM surveys
        LEFT JOIN surveys_answers 
            ON surveys_answers.surveys_id_fk = surveys.surveys_id 
        LEFT JOIN surveys_times 
            ON surveys_answers.answer_time_id = surveys_times.times_id 
        WHERE
            surveys.surveys_id = 5132 AND
            surveys_answers.answer = 1 
        GROUP BY times_id 
    ) t
    ORDER BY t.Commitments DESC
) t
ORDER BY t.times_id