Sql

题目如下 SQL 数据样本 DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名字', `time` datetime(0) NOT NULL COMMENT '时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 106 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (2, '张三', '2021-04-19 00:00:00'); INSERT INTO `user` VALUES (6, '李四', '2021-04-19 01:00:00'); INSERT INTO `user` VALUES (10, '王五', '2021-04-19 02:00:00'); INSERT INTO `user` VALUES (15, '张三', '2021-04-19 00:00:02'); INSERT INTO `user` VALUES (19, '王五', '2021-04-19 02:00:04'); INSERT INTO `user` VALUES (20, '李四', '2021-04-19 03:00:20'); INSERT INTO `user` VALUES (100, '张三', '2021-04-01 00:00:00'); INSERT INTO `user` VALUES (101, '李四', '2021-04-17 11:00:00'); INSERT INTO `user` VALUES (102, '王五', '2021-04-14 19:00:00'); INSERT INTO `user` VALUES (103, '张三', '2021-04-05 02:29:02'); INSERT INTO `user` VALUES (104, '王五', '2021-04-10 11:45:31'); INSERT INTO `user` VALUES (105, '李四', '2021-04-08 20:14:48'); Mysql 5.7 解题参考： SELECT u.`id`, u.`name`, u.`time` FROM ( SELECT *, TIMESTAMPDIFF(SECOND, `time`, `time1`) as delta1, TIMESTAMPDIFF(SECOND, `time`, `time2`) as delta2 FROM ( SELECT (@r := @r + 1) r, user.`id`, user.`name`, user.`time` FROM (SELECT @r := 0) AS t, user ORDER BY user.`name`, user.`time` ) AS b LEFT JOIN ( SELECT (@r1 := @r1 + 1) r1, user.`name` as name1, user.`time` as time1 FROM (SELECT @r1 := -1) AS t1, user ORDER BY name1, time1 ) AS b1 ON b1.r1 = b.r AND b1.`name1` = b.name LEFT JOIN ( SELECT (@r2 := @r2 + 1) r2, user.`name` as name2, user.`time` as time2 FROM (SELECT @r2 := 1) AS t2, user ORDER BY name2, time2 ) AS b2 ON b2.r2 = b.r AND b2.`name2` = b.name ORDER BY b.r ) AS u WHERE (u.delta1 <= 5 AND u.delta1 >= -5) OR (u.delta2 <= 5 AND u.delta2 >= -5) Mysql 8.0 解题参考： SELECT NAME , `time` FROM ( SELECT NAME, `time`, UNIX_TIMESTAMP(`time`) - UNIX_TIMESTAMP(pre_time) AS pre_delta, UNIX_TIMESTAMP(last_time) - UNIX_TIMESTAMP(`time`) AS last_delta FROM ( SELECT NAME, `time`, lag(`time`, 1) over (PARTITION BY NAME ORDER BY `time`) AS pre_time, lead(`time`, 1) over (PARTITION BY NAME ORDER BY `time` ) AS last_time FROM `user` ) t) t WHERE pre_delta <= 5 OR last_delta <= 5 原理 ...

SELECT a.* FROM ( SELECT t1.*, @curr_cnt := t1.type AS curr_type, @rank := IF( @prev_cnt = @curr_cnt, @rank + 1, 1 ) AS rank, @prev_cnt := @curr_cnt AS prev_type FROM `goods` AS t1, ( SELECT @curr_cnt := 0, @prev_cnt := 0, @rank := 0 ) AS t2 ORDER BY t1.type, t1.addtime DESC ) AS a WHERE a.rank <= 10 查询逻辑 对 goods 表进行排序，先按分类 type 正序，再按上架时间 addtime 倒序 `goods` AS t1, ...中间省略 ORDER BY t1.type, t1.addtime 初始化变量，相当于 SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0; SELECT @curr_cnt := 0, @prev_cnt := 0, @rank := 0 按分类进行编号，当 type 与上一条记录的 type 相同时编号 +1, 不同时编号重置为 1 @curr_cnt := t1.type AS curr_type, @rank := IF( @prev_cnt = @curr_cnt, @rank + 1, 1 ) AS rank, @prev_cnt := @curr_cnt AS prev_type 筛选最新上架的 10 条记录 ( 编号 < 10 ) WHERE a.rank <= 10