MySQL | 数据查询DQL语言：分组统计

西安川石 2022-04-11

182

“

每天一个测试技术

只需几分钟

川石信息带你学点更好的

川石信息｜腾讯课堂

免费试学 | 性能测试

统计函数

▲ count(*)

统计查询结果集返回的行数.

#统计客户表中客户人数
SELECT COUNT(*) FROM ecs_users;
#统计商品售价大于500的产品数量
SELECT COUNT(*) FROM ecs_goods
WHERE shop_price > 500;

左右拉动查看完整代码

▲ count(ve)

统计值表达式返回的非空值的个数.

#找出设置了密码提示问题用户人数
SELECT COUNT(*)
FROM ecs_users
WHERE passwd_question IS NOT NULL;


SELECT COUNT(passwd_question) FROM ecs_users;

左右拉动查看完整代码

▲ count(distinct ve)

统计值表达式返回的非空不同值的个数.

#统计商品表中有多少种产品类型
SELECT COUNT(DISTINCT goods_type) FROM ecs_goods;

左右拉动查看完整代码

▲ sum(ve)

统计值表达式返回的非空值的和.

▲ sum(distinct ve)

统计值表达式返回的非空不同值的和.

#统计24号商品的销售数量
SELECT SUM(goods_number) FROM ecs_order_goods
WHERE goods_id = 24;
#统计被购买过的商品的价格之和
SELECT SUM(DISTINCT goods_price)
FROM ecs_order_goods;

左右拉动查看完整代码

▲ avg(ve)

统计值表达式返回的非空值的平均值.

▲ avg(distinct ve)

统计值表达式返回的非空不同值的平均值.

#统计商品表中类型为9的商品的平均市场价格
SELECT AVG(market_price) FROM ecs_goods
WHERE goods_type = 9;
#统计商品表中类型为9的商品的不同价格的平均值
SELECT AVG(distinct market_price) FROM ecs_goods
WHERE goods_type = 9;

左右拉动查看完整代码

▲ max(ve)

统计值表达式返回的非空值的最大值.

▲ min(ve)

统计值表达式返回的非空值的最小值.

#统计商品表中商品的最大市场价格与最小市场价格
SELECT max(market_price),MIN(market_price)
FROM ecs_goods;

#统计产品表中具有最大价格与最小价格的产品信息
SELECT *
FROM ecs_goods
WHERE market_price = (SELECT MAX(market_price) FROM ecs_goods)
OR market_price = (SELECT MIN(market_price) FROM ecs_goods);

左右拉动查看完整代码

数据分组

分组的引入

#统计每种类型的商品的最大市场价格
SELECT distinct goods_type FROM ecs_goods;
#type 1
SELECT MAX(market_price) FROM ecs_goods
WHERE goods_type = 1;
#type 2
SELECT MAX(market_price) FROM ecs_goods
WHERE goods_type = 2;
...

左右拉动查看完整代码

分组定义

指定某列或某些列作为划分的依据,比较这些列值是否相同,具有相同列值的行放在同一组,这样就可以将最初的结果集划分为若干个子集,每个子集称为一个分组.

一般来说有多少个不同的列值就可以分为多少组.

group 分组
by 依据 ...
group by col_name;
group by c1,c2,...,cn;

#按照商品类型分组
SELECT goods_type
FROM ecs_goods
GROUP BY goods_type;

#在ecs_order_info表中查看不同用户选择的寄送方式
SELECT user_id,shipping_id
FROM ecs_order_info
GROUP BY user_id,shipping_id;

左右拉动查看完整代码

分组后统计

语法:

select ...,stat_function(ve),...#stat_function统计函数ve列或计算列
from tabs
where search_condition
group by c1,c2,...,cn;

左右拉动查看完整代码

指定某列或计算列作为统计的对象,将统计函数作用在该对象上,统计函数按照分组自动对每一分组的列值进行统计,每一分组返回一个统计后的结果.

#统计每种类型的商品的最大市场价格
SELECT * FROM ecs_goods;


SELECT goods_type,MAX(market_price)
FROM ecs_goods
GROUP BY goods_type;
#类型编号不太直观,将编号转换为对应的类型名称显示
SELECT gt.cat_name,MAX(market_price)
FROM ecs_goods g INNER JOIN ecs_goods_type gt
ON g.goods_type = gt.cat_id
GROUP BY gt.cat_name;

左右拉动查看完整代码

MYSQL对GROUP BY的非ANSI标准扩展

#统计每种类型的产品的最大价格
SELECT * FROM products;


SELECT goods_type,goods_name,MAX(market_price)
FROM ecs_goods
GROUP BY goods_type;
#MySQL扩展了GROUP BY的用途，可以在SELECT列表中使用GROUP BY子句中未出现过的列
#这在其他数据库(比如Oracle)中是不允许的.

#统计每种类型下每种商品的销售总额
SELECT * FROM ecs_goods;
SELECT * FROM ecs_goods_type;
SELECT * FROM ecs_order_goods;
#统计之前的数据
SELECT gt.cat_name, g.goods_name, og.goods_price, og.goods_number
FROM (ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id)
LEFT OUTER JOIN ecs_order_goods og
ON g.goods_id = og.goods_id
ORDER BY 1, 2;
#统计之后的数据
SELECT gt.cat_name,
g.goods_name,
IFNULL(SUM(og.goods_price * og.goods_number), 0) amount
FROM (ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id)
LEFT OUTER JOIN ecs_order_goods og
ON g.goods_id = og.goods_id
GROUP BY gt.cat_name, g.goods_name
ORDER BY 1, 2;
#IFNULL(p1,p2) 若p1为null,则输出p2;否则输出p1

左右拉动查看完整代码

若统计的数据来源于多个表中,需要将多个表连接起来再进行分组统计.

统计后过滤

是由having子句来实现的.

语法:

select list
from tabs
where search_condition #过滤原始数据
group by grouping_columns
having search_condition; #过滤分组统计后的数据

左右拉动查看完整代码

若要使用having来过滤数据,则必须先使用group by进行分组.

having的用法与where用法相似.

#哪些类型的商品的平均市场价格超过了1000
SELECT gt.cat_name, ROUND(AVG(market_price), 2)
FROM ecs_goods g
INNER JOIN ecs_goods_type gt
ON g.goods_type = gt.cat_id
GROUP BY gt.cat_name
HAVING AVG(market_price) > 1000;
#round(p1,p2) 对p1进行四舍五入,精确到p2位,p2>0小数点后,p2<0小数点前

左右拉动查看完整代码

having子句中的子查询

#哪些类型的商品的平均市场价格超过了所有产品的市场均价
SELECT gt.cat_name, ROUND(AVG(market_price), 2)
FROM ecs_goods g
INNER JOIN ecs_goods_type gt
ON g.goods_type = gt.cat_id
GROUP BY gt.cat_name
HAVING AVG(market_price) > (SELECT AVG(market_price) FROM ecs_goods);