MySQL分组查询和聚合函数
mysql> select uid,count(uid) as nums,sum(amount) as totalamount from t_order group by uid order by totalamount desc; 分组后的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; 关键字的执行顺序 我们看到上面那我们用了 where、group by、having、order by、limit这些关键字,如果一起使用,他们是有先后顺序,顺序错了会导致异常,语法格式如下: select cname from tname 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; 总结 1、分组语法中,select后面出现的字段 要么是group by后面的字段,要么是聚合函数的列,其他类型会报异常:可以自己试试。 2、分组关键字的执行顺序:where、group by、having、order by、limit,顺序不能调换,否则会报异常:可以自己试试。 (编辑:源码门户网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |