Mysql 获取订单表中每个会员的最新订单
SELECT uid, MAX(id) id FROM project_order GROUP BY uid
说明:GROUP BY 分组 col 中必须有分组的字段出现,其他字段只能使用聚合函数,否则会报错
SELECT uid, MAX(expire_time) expire_time,MAX(id) id ,order_no FROM project_order GROUP BY uid
报错:错误代码: 1055
Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'movie.project_order.order_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以看出是sql_mode 问题。这里不再陈述
方法一:
SELECT a.* FROM project_order a WHERE 1 > (SELECT COUNT(*) FROM project_order WHERE uid = a.uid AND id > a.id) ORDER BY a.uid
说明:这种方式可以输出任意你想要的字段
还有以下几种:
方法二:
SELECT a.* FROM project_order a INNER JOIN (SELECT uid, MAX(id) id FROM project_order GROUP BY uid) b ON a.uid = b.uid AND a.id = b.id ORDER BY a.uid
SELECT a.* FROM project_order a WHERE NOT EXISTS (SELECT 1 FROM project_order WHERE uid = a.uid AND id > a.id)
SELECT a.* FROM project_order a WHERE id = (SELECT MAX(id) FROM project_order WHERE uid = a.uid) ORDER BY a.uid
SELECT a.* FROM project_order a, (SELECT uid, MAX(id) id FROM project_order GROUP BY uid) b WHERE a.uid = b.uid AND a.id = b.id ORDER BY a.uid
说明:当uid没有设置为索引的时候,以上执行效率差别明细,当设置uid为索引时,差别几乎可以忽略。