使用Mysql实现Postgresql中窗口函数row_number的功能

1. 描述

需要根据用户id，查询每个人得分第二高的科目信息

2. 表结构及数据

2.1 表结构

CREATE TABLE `t_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `score` double NOT NULL,
  `subject` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

2.2 测试数据

INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(5, 1, 110.0, '数学');
INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(6, 1, 116.5, '语文');
INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(7, 1, 72.0, '英语');
INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(8, 2, 116.5, '语文');
INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(9, 2, 92.0, '英语');
INSERT INTO hz_base.t_score
(id, user_id, score, subject)
VALUES(10, 2, 88.0, '数学');

3. 使用

3.1 类似问题postgresql使用窗口函数ROW_NUMBER实现示例

select * from (
select id, user_id, score, subject,ROW_NUMBER() over (PARTITION by user_id order by score desc) rn from t_score 
) a
where rn = 2

3.2 mysql实现

由于mysql中8.0以下版本缺少对应的窗口函数，如果需要实现该逻辑，可以使用以下sql实现

-- 使用子查询，根据用户id分析，取第二个
select id, user_id, score, subject from t_score s1
where s1.id = (
	select id from t_score
	where user_id = s1.user_id 
	order by score desc
	limit 1 offset 1
);

4. 运行结果

4.1 全部数据

4.2 筛选出的数据