mysql先排序后分组的思路

• mysql 5.6可以这样

  select * from (
  	SELECT factory_id factoryId, is_qualified isQualified 
    	FROM pf_water_report 
    	WHERE factory_id in (1,2,3) 
    	ORDER BY time DESC) a  
  GROUP BY factory_id;
  

• 5.7以后对子查询排序做了优化，子查询全表排序失效，可以这样

  select a.factory_id factoryId, a.is_qualified isQualified 
  from pf_water_report a 
  join (
  	select factory_id,max(time) time 
      from pf_water_report 
      WHERE factory_id in (1,2,3) 
      group by factory_id) b 
  on a.factory_id=b.factory_id and a.time=b.time;
  

| 本文转载自segmentfault.com