mysql - 按类别显示 MySQL 8 中的前 N ​​个分数，不重复

Question

我在 MySQL 8.0.15 中有下表:

CREATE TABLE golf_scores (person TEXT, score INT);
INSERT INTO golf_scores VALUES ('Angela', 40),('Angela', 45),('Angela', 55),('Peter',45),('Peter',55),('Rachel', 65),('Rachel',75),('Jeff',75);

SELECT * FROM golf_scores;
+--------+-------+
| person | score |
+--------+-------+
| Angela |    40 |
| Angela |    45 |
| Angela |    55 |
| Peter  |    45 |
| Peter  |    55 |
| Rachel |    65 |
| Rachel |    75 |
| Jeff   |    75 |
+--------+-------+

我正在尝试获得以下前 3 名分数:

SELECT * FROM golf_scores;
+--------+-------+
| person | score |
+--------+-------+
| Angela |    40 |
| Peter  |    45 |
| Rachel |    65 |
+--------+-------+

换句话说，我想要最好(最低)的 3 高尔夫得分，而不是每个人都重复。我不担心领带；我仍然想要三个结果。

我认为这个查询可以做到这一点:

SELECT person, MIN(score) FROM golf_scores GROUP BY person ORDER BY score LIMIT 3;

但我收到以下错误:

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'records.golf_scores.score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

将 score 添加到 GROUP BY 列表只会返回最低的 3 个总分，而不管 person 列中的重复项。

如何在 MySQL 中获得所需的输出？

Answer 1

由于 order by 子句在 select 子句之后执行，请尝试为 min(score) 设置一个别名。

SELECT person, MIN(score) as min_score FROM golf_scores GROUP BY person ORDER BY min_score LIMIT 3;