mysql - 如何在MySQL中检索group by子句中最大日期的字段基值？

Question

我有一个包含以下数据的表格:

| name | type | score |
+------+------+-------+
|  a   |   1  |  15   |
+------+------+-------+
|  b   |   2  |  12   |
+------+------+-------+
|  c   |   1  |  17   |
+------+------+-------+

现在我必须创建一个查询来获取每种类型的计数以及获得最高分数的学生姓名。这是我的查询:

select name , count(*) as count ,score ,type 
group by type

结果是:

| name | type | score |  count |
+------+------+-------+--------+
|  a   |   1  |  15   |   2    |
+------+------+-------+--------+
|  b   |   2  |  12   |   1    |
+------+------+-------+--------+

但我期望这个结果:

| name | type | score |  count |
+------+------+-------+--------+
|  c   |   1  |  17   |   2    |
+------+------+-------+--------+
|  b   |   2  |  12   |   1    |
+------+------+-------+--------+

Answer 1

SELECT x.*
     , y.total 
  FROM my_table x 
  JOIN 
     ( SELECT type
            , MAX(score) score
            , COUNT(1) total 
         FROM my_table 
        GROUP 
           BY type
     ) y 
    ON y.type = x.type 
   AND y.score = x.score;