mysql如何先排序后group by

Question

我需要查出每个工厂水质记录里最新的一条记录，我目前的sql是这样写的
SELECT factory_id factoryId, is_qualified isQualified FROM pf_water_report WHERE factory_id in (1,2,3) GROUP BY factory_id ORDER BY time DESC
factory_id代表工厂id , is_qualified 代表是否合格
然而由于group by比order by先执行的缘故，我得到的并不是最新的数据，想问问如何先让数据按时间排序，再group by

Answer 1

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;

Answer 2

没有用聚合函数group by和order by没有区别吧。。。。

SELECT factory_id, is_qualified 
FROM pd_water_report as a
WHERE factory_id IN (1,2,3)
ORDER BY factory_id, time DESC

其实只是最新的话

SELECT factory_id, is_qualified 
FROM pd_water_report as a
WHERE factory_id IN (1,2,3) AND time = （
    SELECT max(time) FROM pd_water_report as b
    WHERE b.factory_id = a.factory_id
    GROUP BY factory_id
）

Answer 3

select * from (

SELECT factory_id factoryId, is_qualified isQualified 
FROM pf_water_report 
WHERE factory_id in (1,2,3) 
ORDER BY time DESC) limit 10000 a  

GROUP BY factory_id;

加个limit