mysql - 查找最大的聚合值并存储在新列中

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

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

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

我尝试使用Max（Commitments）AS Highest，但是它不起作用。不创建子查询就可以吗？

目前，我得到以下结果：

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

我想要这样的结果：

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

提前致谢！ :)

最佳答案

您也许可以在此处使用会话变量技巧。定义一个行号会话变量，当它等于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

关于mysql - 查找最大的聚合值并存储在新列中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45838581/