1. AVG()
1)说明:返回表达式的平均值,加上DISTINCT可选项用来返回表达式中不同值的平均值。如果没有匹配到任何数据,AVG()返回NULL.
2)语法:AVG([DISTINCT] expr)
3)实例:(以现有项目的退货表查询为例)
-- 查询退货单表wms_order_id不重复的结果数,不重复的order_money求和,不重复的order_money求平均
SELECT COUNT(DISTINCT r.wms_order_id) AS num,SUM(DISTINCT r.order_money) AS total,AVG(DISTINCT r.order_money) AS money FROM wms_orders_return r;
-- 查询退货单表(不考虑wms_order_id的重复),order_money求和(不考虑重复),order_money求平均(不考虑重复)
SELECT COUNT(r.wms_order_id) AS num,SUM(r.order_money) AS total,AVG(r.order_money) AS money FROM wms_orders_return r;
2. COUNT()
1)说明:返回SELECT语句中查询到的表达式中的值不是NULL的行数,结果是BIGINT类型。如果没有匹配到任何行数,结果返回0.
2)语法:COUNT(expr),COUNT([DISTINCT] expr)
3)实例:(以现有项目的退货表查询为例)
-- 查询退货单表所有的结果数
SELECT COUNT(*) FROM wms_orders_return;
-- 查询退货单表中wms_order_id不重复的结果数
SELECT COUNT(DISTINCT wms_order_id) FROM wms_orders_return;
-- 查询退货单表中不考虑wms_order_id的重复的结果数
SELECT COUNT(wms_order_id) FROM wms_orders_return;
3. MAX()
1)说明:返回一组值中的最大值。如果添加DISTINCT运算符,则MAX函数返回不同值的最大值,它与所有值的最大值相同。 这意味着DISTINCT运算符不会对MAX函数产生任何影响(用不用DISTINCT运算符都可以)。如果没有任何数据匹配,则结果返回NULL.
2)语法:MAX(DISTINCT expr)
3)实例:(以现有项目的退货表查询为例)
-- 查询退货表中退货金额最大的结果
SELECT MAX(r.order_money) FROM wms_orders_return r;
-- 查询退货表中退货金额的最大值,按卖家id分组,金额排序
SELECT r.id,MAX(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id ORDER BY MAX(r.order_money);
-- 查询退货表中退货金额的最大值,按卖家id分组,金额排序,并且退货金额大于100的结果
SELECT r.comp_id,MAX(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id HAVING MAX(r.order_money)>100 ORDER BY MAX(r.order_money);
Group By 的修改
GROUP BY子句可以使用ROLL UP修改,它会导致额外的行被添加到最终的结果。这些行表示高级汇总操作.
2)语法:Group BY * WITH ROLLUP
3)实例:(以现有项目的退货表查询为例)
-- sql1:按供应商分组查询退货总金额
SELECT SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id;
-- sql2:按供应商分组查询退货总金额,并且计算所有退货总金额
SELECT r.comp_id,SUM(r.order_money) FROM wms_orders_return r GROUP BY r.comp_id WITH ROLLUP;
SQL1返回的是各个供应商的退货金额,如果你还想确定所有供应商的退货总金额,就需要自己添加单个值或运行额外的查询。若使用ROLLUP,则不需要进行额外的查询。SQL2的返回结果的最后一行是查询到的所有数据金额的汇总,两个查询结果如下图所示:
MySQL对Group By 的处理
若需要Group By的列不在Select查询字段内,则查询是非法的。例如:
SELECT r.id,r.order_money FROM wms_orders_return r GROUP BY r.comp_id ORDER BY MAX(r.order_money);
在 MySQL5.7.5及以上版本实现了函数依赖检测。如果ONLY_FULL_GROUP_BY模式开启(默认是开启的),则MYSQL会拒绝查询,以上查询就会出错:“[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘uqi_union.r.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”;在5.7.5版本之前,MYSQL未开启检测,并且ONLY_FULL_GROUP_BY默认是关闭的,所以查询不会出错。