欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql,分组(group by)与排序(order by)同时使用

程序员文章站 2022-06-23 11:27:14
先说一下SQL执行顺序:from => join => on => where => group by => having => select => order by => limithaving、where:都是条件查询,区别在于having可以用别、函数。具体区别见:https://blog.csdn.net/W_Kenneth/article/details/110950145测试数据表:reward要求:...

先说一下SQL执行顺序:
from => join => on => where => group by => having => select => order by => limit
having、where:都是条件查询,区别在于having可以用别、函数。
具体区别见:https://blog.csdn.net/W_Kenneth/article/details/110950145

测试数据表:reward
mysql,分组(group by)与排序(order by)同时使用

要求:查询用户最新的奖金数:
结果:
mysql,分组(group by)与排序(order by)同时使用

正确写法:

1、SELECT r.uid,r.money FROM (
SELECT MAX(id) id FROM reward GROUP BY uid
) a LEFT JOIN reward r ON a.id = r.id;

2、SELECT uid, money FROM (
SELECT * FROM reward ORDER BY id desc limit 4
) as a GROUP BY a.uid;
– limit需要大于最后的结果的条数
– mysql5.7之后,子查询中的排序不生效,加上limit使排序生效

错误写法:

SELECT id,uid,money FROM reward ORDER BY id desc GROUP BY uid;
直接报错:mysql,分组(group by)与排序(order by)同时使用

本文地址:https://blog.csdn.net/W_Kenneth/article/details/110926803

相关标签: mysql