MySQL综合查询

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