由于mysql5.7不支持窗口函数,所以不能通过类似oracle和sql server等数据库通过窗口函数来实现,所以需要其他方法来解决。
窗口函数有以下几种,具体用法可以参考网上其余博客:
- ROW_NUMBER() over(partition by columnname order by columnname)
- RANK()over(order by columnname)
- DENSE_RANK()over(order by columnname)
- NTILE()OVER(ORDER BY COLUMNNAME)
这里以订单表为例(t_order):
CREATE TABLE `order` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`time` datetime DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `order` (`id`, `user_id`, `time`, `info`) VALUES (1, 1, '2022-01-21 10:41:49', 'userid=1订单1');
INSERT INTO `order` (`id`, `user_id`, `time`, `info`) VALUES (2, 1, '2022-01-21 10:42:20', 'userid=1订单2');
INSERT INTO `order` (`id`, `user_id`, `time`, `info`) VALUES (3, 2, '2022-01-21 10:42:43', 'userid=2订单1');
INSERT INTO `order` (`id`, `user_id`, `time`, `info`) VALUES (4, 2, '2022-01-21 10:43:15', 'userid=2订单2');
需求如下:查询每个用户最新的一笔订单
分析:1号用户和2号用户最新的订单分别为id为2和4的数据,所以简单来说我们只需要查询出订单id=2和4即可。
方法1:
最新的一条数据实际为时间最大的那行记录,所以我们只需要分组查询出每个用户最大的时间,然后通过时间查询。
SELECT * from `order` a
LEFT JOIN
(SELECT
user_id,
max( time ) time
FROM `order`
WHERE user_id IN ( 1, 2 )
GROUP BY user_id) b on b.user_id=a.user_id
WHERE a.user_id IN ( 1, 2 ) and a.time=b.time
但是这样的sql逻辑是不严谨的,因为考虑极限情况下,一个用户一瞬间下了两笔订单,导致时间相同,这样就会出现多条数据。
所以如果存在这种情况则需要用其他字段来判断最新的定义。
那么则可以考虑通过数据库id,一般来说数据库id主键为,雪花算法id或者自增主键,那么即便是同一瞬间下单,id的先后顺序也一定会有区别,那么就可以通过id大小的顺序来处理。(只使用于id可以判断大小,uuid无法适用)
SELECT * from `order` a
LEFT JOIN
(SELECT
user_id,
max( id ) id
FROM `order`
WHERE user_id IN ( 1, 2 )
GROUP BY user_id) b on b.user_id=a.user_id
WHERE a.user_id IN ( 1, 2 ) and a.id=b.id
方法2:
前文提到mysql5.7不支持窗口函数,但是在mysql8.0中已经支持窗口函数,所以可以通过窗口函数来实现
SELECT * from
(select *,
row_number() over(partition by user_id order by time desc) as row_num #关键行号
from `order` where user_id in (1,2)) a
WHERE a.row_num=1
结果如下: