because mysql5.7 Window functions are not supported , So we can't pass the similar oracle and sql server And other databases are implemented through window functions , So we need other methods to solve it .
There are several window functions , For specific usage, please refer to other blogs on the Internet ：
* 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)
Take the order form as an example （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 order 1'); INSERT INTO `order` (`id`, `
user_id`, `time`, `info`) VALUES (2, 1, '2022-01-21 10:42:20', 'userid=1 order 2');
INSERT INTO `order` (`id`, `user_id`, `time`, `info`) VALUES (3, 2, '2022-01-21
10:42:43', 'userid=2 order 1'); INSERT INTO `order` (`id`, `user_id`, `time`, `info`)
VALUES (4, 2, '2022-01-21 10:43:15', 'userid=2 order 2');
The requirements are as follows ： Query the latest order of each user
analysis ：1 User number and 2 The latest orders of user No. are id by 2 and 4 Data of , So simply put, we just need to query the order id=2 and 4 that will do .
<> method 1：
The latest data is actually the record with the largest time , So we only need to query the maximum time of each user in groups , Then query by time .
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_idIN ( 1, 2 ) and a.time=b.time
But like this sql Logic is not rigorous , Because considering the limit case , A user placed two orders in an instant , Cause the same time , In this way, multiple pieces of data will appear .
So if this situation exists, you need to use other fields to judge the latest definition .
Then you can consider using the database id, Generally speaking, database id Primary key is , Snowflake algorithm id Or auto increment primary key , So even at the same moment ,id There must be a difference in the order of , Then you can pass id In order of size .（
Only for id Can judge the size ,uuid Not applicable ）
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_idIN ( 1, 2 ) and a.id=b.id
<> method 2：
As mentioned above mysql5.7 Window functions are not supported , But in mysql8.0 Window functions are already supported in , So it can be realized through window function
SELECT * from (select *, row_number() over(partition by user_id order by time
desc) as row_num # Key line number from `order` where user_id in (1,2)) a WHERE a.row_num=1
give the result as follows ：
Technology