来自群友的题目,做个笔记记录答题过程~~~~
1、需求:使用一个SOL取出每位同学最高分的一条记录,如果某个同学最高分出现多条则取出序号值较大的一条记录。最终结果应与下表相同。
2、建表语句:
CREATE TABLE `kmb` (
`id` int auto_increment PRIMARY key not null COMMENT '序号',
`name` VARCHAR(10) COMMENT '姓名',
`class` VARCHAR(10) COMMENT '班级',
`km` VARCHAR(10) COMMENT '科目',
`fs` int not null COMMENT '分数'
)
INSERT into kmb VALUES('1','张三','A','数学','99');
INSERT into kmb VALUES('2','张三','A','语文','97');
INSERT into kmb VALUES('3','张三','A','数学','98');
INSERT into kmb VALUES('4','张三','A','语文','99');
INSERT into kmb VALUES('5','李四','A','数学','99');
INSERT into kmb VALUES('6','李四','A','语文','97');
INSERT into kmb VALUES('7','李四','A','数学','99');
INSERT into kmb VALUES('8','李四','A','语文','93');
3、答案
WITH a as(
SELECT *,
RANK() over (PARTITION by name ORDER BY fs desc,id desc) rn -- ORDER BY 后面可增加多个数值
from kmb
)
SELECT
max(id) as 序号,
name as 姓名,
class as 班级,
km as 科目,
fs as 成绩
from a
WHERE rn=1
GROUP BY name,class,km,fs;
4、结果展示: