SQL 先排序再分组优化方案

传统写法：

SELECT * FROM
( SELECT * FROM report_member_advance WHERE company_id = '001' ORDER BY id DESC) a GROUP BY a.member_id
LIMIT 20;

效率低下，使用pageHelp分页还会出现总条数问题。

 

优化方案如下：使用内链接

SELECT * FROM report_member_advance rma INNER JOIN
(SELECT MAX(id) AS id FROM report_member_advance WHERE company_id = '001' GROUP BY member_id) t ON rma.id = t.id limit 20;

亲测，效率提高了差不多10倍。