MySQL分组查询和聚合函数

mysql> select uid,count(uid) as nums,sum(amount) as totalamount from t_order group by uid order by totalamount desc;
+-----+------+-------------+
| uid | nums | totalamount |
+-----+------+-------------+
|  2 |  4 | 375.53   |
|  1 |  3 | 345.65   |
|  4 |  1 | 199.99   |
|  3 |  2 | 125.51   |
+-----+------+-------------+
4 rows in set

分组后的limit 限定

limit限定要害字一样平常放在语句的最末端，好比基于我们上面的搜刮，我们再limit 1，只取出斲丧额最高的那条，其他跳过。

mysql> select uid,count(uid) as nums,sum(amount) as totalamount from t_order group by uid order by totalamount desc limit 1;
+-----+------+-------------+
| uid | nums | totalamount |
+-----+------+-------------+
|  2 |  4 | 375.53   |
+-----+------+-------------+
1 row in set

要害字的执行次序

我们看到上面那我们用了 where、group by、having、order by、limit这些要害字，假如一路行使，他们是有先后次序，次序错了会导致非常，语法名目如下：

select cname from tname
 where [原表查询前提]
 group by [分组表达式]
 having [分组过滤前提]
 order by [排序前提]
 limit [offset,] count;

mysql> select uid,count(uid) as nums,sum(amount) as totalamount from t_order where time > '2019-08-01' group by uid having totalamount>100 order by totalamount desc limit 1;
+-----+------+-------------+
| uid | nums | totalamount |
+-----+------+-------------+
|  2 |  3 | 273.21   |
+-----+------+-------------+
1 row in set

总结

1、分组语法中，select后头呈现的字段 要么是group by后头的字段，要么是聚合函数的列，其他范例会报非常：可以本身试试。

2、分组要害字的执行次序：where、group by、having、order by、limit，次序不能变更，不然会报非常：可以本身试试。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!