我有一张表,其中包含以下数据:
| 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;