我使用的是MySQL8.0.23版本
给出的表格是：-
+-----+--------+-----------+-------+-----------|
| id | name | subject | marks | city |
+-----+--------+-----------+-------+-----------|
| 101 | ravi | maths | 70 | Mumbai |
| 103 | Viaan | english | 26 | Bangalore |
| 104 | varun | chemistry | 95 | delhi |
| 105 | rishab | biology | 69 | delhi |
| 108| Mihika | maths | 78 | Kolkata |
| 110| Ishaan | english | 39 | Bangalore |
+-----+--------+-----------+-------+-----------|
我想要得到每个城市各科最高分学生的完整数据
select subject, city, max(marks) as highest
from students
group by subject, city
order by subject, city;
--
select subject
, city
, max(marks) as highest
from students
group
by subject
, city
order
by subject
, city;
+-----------+-----------+---------+
| subject | city | highest |
+-----------+-----------+---------+
| biology | Bangalore | 87 |
| biology | Chennai | 58 |
| biology | delhi | 82 |
| biology | Jaipur | 52 |
| biology | Kolkata | 92 |
| biology | Lucknow | 98 |
| chemistry | Bangalore | 84 |
| chemistry | Chennai | 64 |
| chemistry | delhi | 95 |
| chemistry | Jaipur | 83 |
| chemistry | Kolkata | 45 |
| chemistry | Lucknow | 100 |
| chemistry | Mumbai | 87 |
我已经用过这个了，但是我还是不能看到那个学生的名字和id
下面是使用窗口函数的一种方法：
select * from
(
select * , rank() over (partition by subject, city order by mark desc) rn
from yourtable
) t
where rn = 1
