在MySQL 8中显示前N名得分，没有按类别重复的得分 [英] Show top N scores in MySQL 8 without duplicates by category

问题描述

我在MySQL 8.0.15中具有下表:

I have the following table in 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个得分:

I am trying to get the following top 3 scores:

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

换句话说，我想要最好的(最低的)3高尔夫球成绩，而不必亲自重复.我不担心关系；我仍然只想要三个结果.

In other words, I want the best (lowest) 3 golf score without having duplicates by person. I'm not worried about ties; I'd still just like three results.

我认为此查询可以做到:

I thought this query would do it:

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

但出现以下错误:

错误1055(42000):ORDER BY子句的表达式#1不在GROUP BY子句中，并且包含未聚合的列'records.golf_scores.score'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by

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列中的重复项.

Adding score to the GROUP BY list just returns the lowest 3 scores total, regardless of duplicates in the person column.

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

How can I get the desired output in MySQL?

推荐答案

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

since order by clause is executed after select clause,try putting an aliases for min(score) .

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

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

这篇关于在MySQL 8中显示前N名得分，没有按类别重复的得分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！