查询分组后id最大的一条记录

Linux系统-部署-运维系列导航

 

-- 连表

SELECT t.* from test_table t 
inner join 
(select t1.`name`,max(t1.id) id from test_table t1 group by t1.`name`) t2
on t.id = t2.id;

 

-- 子查询

SELECT t.* from test_table t 
where t.id=
(select max(t1.id) id from test_table t1 where t1.`name` = t.`name`)

 

-- 左连接

select * from test_table t 
left JOIN test_table t1 on t.`name`=t1.`name` and t.id<t1.id
where t1.id is null;

 

-- not EXISTS

select * from test_table t 
where not EXISTS (select 1 from test_table t1 where t1.name=t.`name` and t1.id>t.id);