1、查询满足某个字段相同的最新一条数据
场景:查询项目进度表中的最新进度
-
先按照时间desc排序,查询该层使用group by语句会按照排序的数据的第一条取出来(不支持低于MySQL5.7版本)
select project_id,content createon from (select project_id,content from pms_peoject_progress order by createon desc) a group by a.createon
-
使用not exists,通过相同project_id的不同创建时间进行比较
select project_id,content from pms_project_progress ppp1 where not exists (select * from pms_project_progress ppp2 where ppp1.project_id = ppp2.project_id and ppp1.createdon < ppp2.createdon )
- 使用内关联方式(先查询最后一条数据的时间)
select * from pms_project_progress a
right JOIN
( select project_id,content, MAX(createdon) createdon from pms_project_progress group by project_id) b
on a.project_id = b.project_id and a.createdon = b.createdon