mysql5.7 先排序ORDER BY,后分组 GROUP BY

场景:在数据库中,当同一个目标有多个值时,需要按序找出其对应最大/最小的那一条,或者是按序的第多少条。使用简单的order by 和 group by无法满足,因为mysql是先执行group by,后执行order by的,这样先group by就不是想要的排序结果。

image.png

解决:可以使用子查询的limit,然后再查询子查询出来的表。

需要注意一点:
SELECT * FROM
(SELECT * FROM dmall.d_appoint_order ORDER BY order_id DESC) temp
GROUP BY temp. order_id
发现排序居然没有生效,顿时很惊讶

explain 查看执行计划,发现在没有 limit 的情况,会少了一个derived 操作,mysql会认为这时候不需要排序,内部做了优化,所以这种情况下加limit限制就可以了,如下

SELECT * FROM
(SELECT * FROM dmall.d_appoint_order ORDER BY order_id DESC limit 100) temp
GROUP BY temp. order_id

SELECT tmp.id,tmp.appointNo,tmp.orderId FROM (  
    SELECT 
    id,
    appoint_no AS appointNo, 
    company_id AS companyId, 
    order_id AS orderId, 
    FROM dmall.d_appoint_order 
    WHERE 
    appoint_no IN ( "20200618000002", "20200618000003", "20200618000004" ) 
    AND is_sub_order=1 
    AND order_status=1 
    AND is_cancel=0 
    ORDER BY appoint_no ASC ,order_id DESC LIMIT 100
) tmp
GROUP BY tmp.appointNo
image.png

推荐阅读更多精彩内容