业务场景：取一张表里面最新的一条数据，以最新的一条数据为基准进行相关业务操作。
CREATE TABLE `t_biz` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`create_date` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO t_biz (id, name, create_date) VALUES(1, 'Tom', '2022-01-21');
INSERT INTO t_biz (id, name, create_date) VALUES(2, 'Tom', '2022-01-02');
INSERT INTO t_biz (id, name, create_date) VALUES(3, 'Cat', '2022-02-01');
INSERT INTO t_biz (id, name, create_date) VALUES(4, 'Cat', '2022-02-02');
INSERT INTO t_biz (id, name, create_date) VALUES(5, 'Cat', '2022-02-03');
5.7
- 相关子查询
select m.id, m.name,m.create_date
from t_biz m where not EXISTS (select 1 from t_biz i where i.name=m.name and m.create_date<i.create_date)
- 关联查询
select m.id, m.name,m.create_date
from t_biz m , (select i.name, max(i.create_date) create_date from t_biz i group by i.name ) i
where m.name =i.name and m.create_date =i.create_date
8.0
- rank 函数
select m.id, m.name,m.create_date
from (select id, name,create_date, rank() over(partition by name order by create_date desc) rank_seq from t_biz ) m
where m.rank_seq = 1;
