SQL-DQL中聚合函数、分组查询及HAVING过滤

目录

1、聚合函数（掌握SQL中聚合函数的使用）

2、分组查询（掌握SQL中GROUP  BY分组聚合的使用）

3、HAVING过滤（掌握SQL中HAVING过滤的使用）

HAVING和WHERE

---

1、聚合函数（掌握SQL中聚合函数的使用）

聚合函数又叫组函数、统计函数，用来对表中的指定列数据进行统计计算。

• 注：聚合函数经常会结合分组（GROUP  BY）来使用

SELECT

        聚合函数(列名) ,

        ...

FROM  表名 ;

常用的聚合函数：

• COUNT(col)：求指定列的总记录        count：计数
• MAX(col)：求指定列的最大值             maximum：最大值
• MIN(col)：求指定列的最小值              minimum：最小值
• SUM(col)：求指定列的和                    sun：总和
• AVG(col)：求指定列的平均值             average：平均数

注意：聚合函数的计算会忽略NULL值。

聚合函数统计 => count()求总记录数，max()最大值，min()最小值，avg()平均值，sum()求和

• 返回goods商品表中总记录数 => count()建议统计主键类

select count(id) as cnt from goods;  
select count(*) as cnt from goods;

• 返回评分中的最大值

select max(score) as max_score from goods;

• 返回评分中的最小值

select min(score) as min_score from goods;

• 返回商品中的平均价格

select avg(price) as avg_price from goods;

• 返回购买所有商品所需的总价格

select sum(price) as sum_price from goods;

注：AS cnt 为另起别名cnt,聚合函数中最好将输出的新结果另起别名

 

2、分组查询（掌握SQL中GROUP  BY分组聚合的使用）

GROUP  BY分组聚合分为两步：先分组，再聚合。

• 先分组：把表数据按照指定列的值进行划分，值相同的数据划分到同一组
• 再聚合：分别针对每一组数据使用聚合函数进行统计

SELECT

        分组字段 ,

        聚合函数 ,

        ...

FROM  表名

GROUP  BY  分组字段 , ... ;

GROUP BY分组子句 => 经常和聚合函数结合在一起使用

• 分组怎么使用

select category_id from goods group by category_id;
select is_self from goods group by is_self;

• 分组 + 聚合，求每个分类的商品数量

select category_id,count(*) as cnt from goods group by category_id;

• 多字段组合 + 聚类，求每个分类中自营和非自营商品的数量 （有几种组合：自营和1组合、非自营和1组合等）

select category_id,is_self,count(*) as cnt from goods group by category_id, is_self;

 注意：分组之后，SELECT后面查询的字段，必须满足下列2种情况之一：

• 情况1：字段是分组字段【即在GROUP  BY之后出现】
• 情况2：字段被聚合函数统计【聚合函数（字段）】

3、HAVING过滤（掌握SQL中HAVING过滤的使用）

SELECT

        分组字段 ... ,

        聚合函数(字段) ...

FROM  表名

GROUP  BY  分组字段1 , 分组字段2 ...

HAVING  条件表达式 ;

HAVING子句 => 针对GROUP BY分组进行进一步筛选，虽然和WHERE类似，都是进行数据筛选，但是HAVING发生分组之后；WHERE发生在分组之前。

• having功能上与where类似，都可以进行数据的过滤，简单语句的情况下，having子句可以代替where子句

select * from goods where price > 5000;
select * from goods having price > 5000;

• 有了group by以后，两者区别很大，where发生在前，having发生在后
• 统计每个分类商品的平均价格，并筛选出平均价格低于1000的分类

select category_id,avg(price) from goods group by category_id having avg(price) < 1000;
select category_id,avg(price) as avg_price from goods group by category_id having avg_price < 1000;

HAVING和WHERE

HAVING和WHERE有什么区别？

• HAVING是对分组聚合之后的结果进行过滤，WHERE是在对分组前的数据进行过滤。
• WHERE  → GROUP  BY → 聚合 → HAVING
• HAVING后面可以使用聚合函数（统计函数），WHERE后面不可以使用聚合函数。

• 统计自营商品中，每个分类商品的平均价格，并筛选出平均价格高于2000的分类

select category_id,avg(price) as avg_price from goods where is_self = '自营' group by category_id having avg_price > 2000;