Mysql时间轴数据获取同一天数据的前三条实例介绍

作者:袖梨 2022-06-29

本篇文章小编给大家分享一下Mysql时间轴数据获取同一天数据的前三条实例介绍,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

创建表数据

CREATE TABLE `praise_info` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `pic_id` varchar(64) DEFAULT NULL COMMENT '图片ID',
 `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
 KEY `pic_id` (`pic_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3647 DEFAULT CHARSET=utf8 COMMENT='图片表';

添加数据省略

时间轴前2条数据

SELECT * FROM
(
SELECT *, @num := if(@created_time = DATE_FORMAT(created_time, '%Y-%m-%d'), @num := @num + 1, 1) as row_num,
@created_time := DATE_FORMAT(created_time, '%Y-%m-%d') as axisTime FROM praise_info
order by id desc
) AS temp
WHERE row_num < 3;

相关文章

精彩推荐