- 查各个用户下单最早的一条记录
- 查各个用户下单最早的前两条记录
- 查各个用户第二次下单的记录
一、建表填数据：
SET NAMES utf8mb4;
-- 取消外键约束
SET FOREIGN_KEY_CHECKS = 0; -- ----------------------------
-- Table structure for order_table
-- ----------------------------
DROP TABLE IF EXISTS `order_table`;
CREATE TABLE `order_table` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- ----------------------------
-- Records of order_table
-- ----------------------------
BEGIN;
INSERT INTO `order_table` VALUES (1, 1, '2019-03-03 16:29:58');
INSERT INTO `order_table` VALUES (2, 1, '2019-03-04 16:30:17');
INSERT INTO `order_table` VALUES (3, 1, '2019-03-05 16:30:29');
INSERT INTO `order_table` VALUES (4, 2, '2019-03-03 16:35:23');
INSERT INTO `order_table` VALUES (5, 2, '2019-03-04 16:35:34');
COMMIT;
-- 添加外键约束
SET FOREIGN_KEY_CHECKS = 1;
二、解决上述三个问题
1、查各个用户下单最早的一条记录
select ot.* FROM order_table ot
where create_time = (
SELECT min(create_time) FROM order_table where user_id = ot.user_id
) order by ot.user_id
结果：
2、查各个用户下单最早的前两条记录
-- 分组最小的前两条
select * from order_table ot
where 2 > (select count(*) from order_table where user_id = ot.user_id and create_time < ot.create_time )
order by ot.user_id, ot.create_time
结果：
3、查各个用户第二次下单的记录
-- 分组排序，取create_time倒数第二小的数据
select * FROM (
-- 按user_id分组、create_time asc排序、取create_time最小和倒数第二小的值
select * from order_table oot
where 2 > (
select count(*)
from order_table
where user_id = oot.user_id and create_time < oot.create_time
)
order by oot.user_id, oot.create_time
) as ttt where
ttt.order_id not in
( -- 按user_id分组、create_time asc排序、取create_time最小的值
select ot.order_id FROM order_table ot
where create_time = (
SELECT min(create_time) FROM order_table where user_id = ot.user_id
) order by ot.user_id
)
结果：
参考链接：http://www.manongjc.com/article/1082.html