按城市选择各学科组中所有得分最高的学生的数据

Question

我使用的是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

Answer 1

下面是使用窗口函数的一种方法：

select * from 
   ( 
    select * , rank() over (partition by subject, city order by mark desc) rn
    from yourtable
   ) t
where rn = 1