SQL （九）分组数据（group by子句, having子句）

不念不忘少年蓝@ 2023-02-21 06:57 5阅读 0赞

group by子句和having子句都是select子句

返回供应商DLL01的总产品数

select count(*) as num_prods, vend_id
    from products
    where vend_id = 'DLL01';

![在这里插入图片描述][20200629200902970.png]

# 用分组把数据分为多个逻辑组，对每个组进行聚集计算 #

## 创建分组：select语句的group by子句 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]

select vend_id, count(*) as num_prods
    from products
    group by vend_id;

group by指示DBMS按照vend\_id分组数据，然后对每个组再聚集计算。  
![在这里插入图片描述][2020062920130766.png]

注意不用group by会报错：

select vend_id, count(*) as num_prods
    from products;

## 过滤分组：having子句 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 1]  
厉害了，

*  having子句非常类似于where子句，目前学过的所有类型的where子句都可以用having替代
 *  having也是过滤，但是过滤的是分组，而where过滤的是行  
    ![在这里插入图片描述][2020062920325350.png]  
    ![在这里插入图片描述][20200629204102684.png]

### 示例1 ###

select cust_id, count(*) as orders
    from orders
    group by cust_id
    having count(*) >=2;

![在这里插入图片描述][20200629203052351.png]  
如果没有having子句，则输出如下，所以having子句确实过滤了3个分组。这里的过滤是基于分组聚集值，而不是特定行的值  
![在这里插入图片描述][20200629203106840.png]

### 示例2：having和where一起用 ###

select vend_id, count(*) as num_prods
    from products
    where prod_price >= 4
    group by vend_id
    having count(*) >= 2;

![在这里插入图片描述][20200629203715842.png]  
看看供应商和价格情况

select vend_id, prod_price
    from products;

![在这里插入图片描述][20200629203929780.png]

可以看到，where语句先过滤了价格小于4的，然后再根据行数大于2来聚集

# 分组的排序 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 2]

## 使用group by应该也给出order by，不要只依赖group by ##

没有用order by

select order_num, count(*) as items
    from orderitems
    group by order_num
    having count(*) >= 3;

出来的顺序不知道是什么顺序  
![在这里插入图片描述][20200629204614708.png]  
给出order by，按照items数量先排序，items相同的再按照订单号排序

select order_num, count(*) as items
    from orderitems
    group by order_num
    having count(*) >= 3
    order by items, order_num;

![在这里插入图片描述][2020062920464633.png]  
![在这里插入图片描述][20200629204901709.png]

# select子句的顺序 #

终于要总结他们的顺序了  
![在这里插入图片描述][20200629204941816.png]

# 总结 #

*  虽然having和where很相似，但是要记住，having一定要和group by一起用，是用于过滤分组；而where是用于普通的行级过滤
 *  掌握好where和having的差异
 *  掌握好order by 和group by 的差异
 *  掌握好设了select子句的顺序

[20200629200902970.png]: https://img-blog.csdnimg.cn/20200629200902970.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200629202658214.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[2020062920130766.png]: https://img-blog.csdnimg.cn/2020062920130766.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200629202809976.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[2020062920325350.png]: https://img-blog.csdnimg.cn/2020062920325350.png
[20200629204102684.png]: https://img-blog.csdnimg.cn/20200629204102684.png
[20200629203052351.png]: https://img-blog.csdnimg.cn/20200629203052351.png
[20200629203106840.png]: https://img-blog.csdnimg.cn/20200629203106840.png
[20200629203715842.png]: https://img-blog.csdnimg.cn/20200629203715842.png
[20200629203929780.png]: https://img-blog.csdnimg.cn/20200629203929780.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200629204334373.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[20200629204614708.png]: https://img-blog.csdnimg.cn/20200629204614708.png
[2020062920464633.png]: https://img-blog.csdnimg.cn/2020062920464633.png
[20200629204901709.png]: https://img-blog.csdnimg.cn/20200629204901709.png
[20200629204941816.png]: https://img-blog.csdnimg.cn/20200629204941816.png