我有一张表,其中包含以下数据:

| 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    |
+------+------+-------+--------+

最佳答案

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;

10-07 19:38
查看更多