【开源中国 APP 全新上线】“动弹” 回归、集成大模型对话、畅读技术报告”
表结构:
CREATE TABLE `user_game_record` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`user_nickname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '用户昵称',
`score` int(11) DEFAULT NULL COMMENT '分数',
`created_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `index_user_id_score` (`user_id`,`score`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='2021过年活动-用户每次成绩记录';
表中数据:
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (1, 10001, '张三', 80, '2021-01-19 09:40:55');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (2, 10003, '王五', 80, '2021-01-12 08:41:11');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (3, 10004, '李四', 72, '2021-01-21 09:41:55');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (4, 10004, '李四', 87, '2021-01-12 09:41:59');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (5, 10001, '张三', 63, '2021-01-23 09:42:43');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (6, 10007, '赵六', 10, '2021-01-27 09:43:23');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (7, 10009, '孙七', 92, '2021-01-24 09:43:28');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (8, 10007, '赵六', 77, '2021-01-06 09:44:34');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (9, 10001, '张三', 66, '2021-01-31 09:44:51');
INSERT INTO `user_game_record`(`id`, `user_id`, `user_nickname`, `score`, `created_time`) VALUES (10, 10003, '王五', 80, '2021-01-19 09:45:15');
数据截图:
需求:
1. 查出每个人的最高分数排行榜(这个简单)。
2. 以及排在当前登录人前面一人以及后面一人的排名和名词
需求一:
SELECT
id,
user_id,
user_nickname,
max( score ) AS score
FROM
user_game_record
GROUP BY
user_id
ORDER BY
score DESC,
id
结果:
需求二:
排在当前登录人前面一位的排名以及分数,和当前登录人后面以为的排名和分数,不知何解最优
如何获取张三的名次呢
mysql好像有个窗口函数,不知道有没有用