mysql5.7之前可以直接先排序后分组 (分组后获取排序第一的数据)
SELECT * FROM
( SELECT * FROM table t
ORDER BY t.pay_date desc
) x
GROUP BY x.payment_id
5.7版本的mysql在执行这条sql语句的时候缺少了一个derived的操作,
mysql5.7对子查询进行了优化,认为子查询中的order by可以进行忽略,
只要Derived table里不包含如下条件就可以进行优化:
①、UNION clause
②、GROUP BY
③、DISTINCT
④、Aggregation
⑤、LIMIT or OFFSET
SELECT * FROM
( SELECT * FROM table pp
ORDER BY pp.pay_date desc
LIMIT 10000
) x
GROUP BY x.payment_id
SELECT * FROM
( SELECT * FROM table pp
GROUP BY pp.id
ORDER BY pp.pay_date desc
) x
GROUP BY x.payment_id
SELECT * FROM
( SELECT DISTINCT * FROM table pp
ORDER BY pp.pay_date desc
) x
GROUP BY x.payment_id;
-- 通用方法 PG MYSQL 都可使用
SELECT a.* FROM table a
( SELECT Max(pay_date) as max_pay_date,payment_id FROM table
group by payment_id
) b on a.payment_id = b.payment_id and a.pay_date = b.max_pay_date
--有相同数据可以使用distinct过滤