作者 | 川石信息
来源 | 今日头条
一、统计函数
▲ 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);
二、数据分组
由group by子句来实现的.
select list
from tabs
where search_condition
group by grouping_columns;
(1)分组的引入
#统计每种类型的商品的最大市场价格
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;
(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;
(1)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小数点前
(1)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);
