美文网首页
MySQL | 数据查询DQL语言:分组统计

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

作者: 82a7fe2508f4 | 来源:发表于2022-04-26 10:39 被阅读0次

    一、统计函数

    ▲ 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);

    相关文章

      网友评论

          本文标题:MySQL | 数据查询DQL语言:分组统计

          本文链接:https://www.haomeiwen.com/subject/naecyrtx.html