在MySQL 8中按类别显示前N行，而在另一个类别中不显示重复行 [英] Show top N rows by category in MySQL 8 without duplicates in another category

问题描述

类似于此问题，我在MySQL 8.0.15中具有下表：

Similar to this question, I have the following table in MySQL 8.0.15:

CREATE TABLE golf_scores (id INT PRIMARY KEY AUTO_INCREMENT, person TEXT, score INT, age INT);
INSERT INTO golf_scores (person, score, age) VALUES ('Angela', 40, 25),('Angela', 45, 25),('Angela', 55, 25),('Peter',45, 32),('Peter',55,32),('Rachel', 65, 35),('Rachel',75,35),('Jeff',75, 16);
SELECT * FROM golf_scores;
+----+--------+-------+------+
| id | person | score | age  |
+----+--------+-------+------+
|  1 | Angela |    40 |   25 |
|  2 | Angela |    45 |   25 |
|  3 | Angela |    55 |   25 |
|  4 | Peter  |    45 |   32 |
|  5 | Peter  |    55 |   32 |
|  6 | Rachel |    65 |   35 |
|  7 | Rachel |    75 |   35 |
|  8 | Jeff   |    75 |   16 |
+----+--------+-------+------+

我们要选择以下最佳 3行：

We want to select the following "best" 3 rows:

+----+--------+-------+------+
| id | person | score | age  |
+----+--------+-------+------+
|  1 | Angela |    40 |   25 |
|  4 | Peter  |    45 |   32 |
|  6 | Rachel |    65 |   35 |
+----+--------+-------+------+

换句话说，最低的3个高尔夫得分没有人，以及该行的其他列的重复。我不担心关系；我仍然只希望得到三个结果。

In other words, the lowest 3 golf scores without having duplicates by person, and also the other columns from that row. I'm not worried about ties; I'd still just like three results.

查询 SELECT人，MIN（score）为min_score FROM golf_scores GROUP BY人ORDER BY min_score LIMIT 3; 给出正确的行，但仅限于 person 和score`列。当我尝试像这样修改它时：

The query SELECT person, MIN(score) as min_score FROM golf_scores GROUP BY person ORDER BY min_score LIMIT 3; gives the right rows, but is limited to the columns person and score`. When I try to modify it like this:

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

我收到此错误：

错误1055（42000）：SELECT列表的表达式＃1不在GROUP BY子句中，并且包含未聚合的列'records.golf_scores.id'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by

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

不兼容我还尝试了使用 SELECT id，DISTINCT人，得分来消除重复的名称，年龄从golf_scores到ORDER BY得分上限3 ，但出现错误

I also tried eliminating duplicate names with SELECT id, DISTINCT person, score, age FROM golf_scores ORDER BY score LIMIT 3 but I get an error

错误1064（42000）：您您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以找到正确的语法，以在 DISTINCT人员，在第1行从golf_scores ORDER BY得分限制3附近得分

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT person, score FROM golf_scores ORDER BY score LIMIT 3' at line 1

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

How can I get the desired output in MySQL?

推荐答案

这里是一种方法：

SELECT x.* 
  FROM golf_scores x
  JOIN 
     ( SELECT MIN(id) id FROM
            ( SELECT a.* 
                FROM golf_scores a 
                JOIN 
                   ( SELECT person, MIN(score) score FROM golf_scores GROUP BY person ) b 
                  ON b.person = a.person 
                 AND b.score = a.score 
            ) n
        GROUP
           BY person
            , score 
     ) y
    ON y.id = x.id
 ORDER 
    BY x.score LIMIT 3;

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