MySQL之分组数据

￡神魔★判官ぃ 2022-05-24 03:07 199阅读 0赞

**1、group by创建分组**

在MySQL中，分组是在select语句中的group by子句中建立的，比如：

SELECT vend-id, COUNT(*) as num_prods FROM products GROUP BY vend_id;

这条SQL语句指定了2个列，group by指示MySQL按照vend\_id排序并且分组（如果使用group by，则不必指定要计算的每个组）

group by子句指示指示MySQL分组数据，然后都每个组而不是整个结果集进行聚集；关于group by使用，请注意以下规则：

①group by子句可以包含任意数目的列（使得对分组进行嵌套，为数据分组提供更细致的控制）；

②如果在group by子句中嵌套分组，数据将在最后规定的分组上进行汇总，即：建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据）；

③group by子句中列出的每个列都必须是检索列或有效的表达式（**但不能是聚集函数**），如果在select中使用表达式，则必须在group by子句中**指定相同的表达式（不能使用别名）**；

④**除了聚集计算语句外，select中每个列都必须在group by子句中给出；**

⑤如果分组列中具有null值，则null将作为一个分组返回（如果列中有多行null值，他们将分为一组）；

⑥**group by子句必须出现在where子句之后，order by子句之前；**

PS：使用with rollup关键字，可以得到**每个分组以及每个分组汇总级别（针对每个分组）的值。**

可以对比着看是否使用rollup的两种情况:

mysql> select dep,pos,avg(sal) from employee group by dep,pos;  
    +------+------+-----------+  
    | dep | pos | avg(sal) |  
    +------+------+-----------+  
    | 01 | 01 | 1500.0000 |  
    | 01 | 02 | 1950.0000 |  
    | 02 | 01 | 1500.0000 |  
    | 02 | 02 | 2450.0000 |  
    | 03 | 01 | 2500.0000 |  
    | 03 | 02 | 2550.0000 |  
    +------+------+-----------+  
    6 rows in set (0.02 sec)

自动剔除含有NULL值的行，没有显示NULL的行。

mysql> select dep,pos,avg(sal) from employee group by dep,pos with rollup;  
    +------+------+-----------+  
    | dep | pos | avg(sal) |  
    +------+------+-----------+  
    | 01 | 01 | 1500.0000 |  
    | 01 | 02 | 1950.0000 |  
    | 01 | NULL | 1725.0000 |  
    | 02 | 01 | 1500.0000 |  
    | 02 | 02 | 2450.0000 |  
    | 02 | NULL | 2133.3333 |  
    | 03 | 01 | 2500.0000 |  
    | 03 | 02 | 2550.0000 |  
    | 03 | NULL | 2533.3333 |  
    | NULL | NULL | 2090.0000 |  
    +------+------+-----------+  
    10 rows in set (0.00 sec)

将NULL的行也全部显示出来了。

**需要注意的是，使用有 WITH ROLLUP 子句的 GROUP BY 语句时，不能再使用 ORDER BY 语句对结果集进行排序，如果对返回的结果顺序不满意，需要应用程序获得结果后在程序中进行排序。**

**2、having过滤分组**

where子句都可以用having代替，区别在于where过滤行，having过滤分组；having支持所有的where操作符，比如：

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

这条SQL语句中的having子句过滤count（\*）>=2（2个以上的分组）的那些分组；

having和where的区别：

**where在数据分组前进行过滤，having在数据分组后进行过滤；**where排除的行不包括在分组中（这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组）

having和where可以同时使用，比如：

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

这条SQL语句中，where子句过滤掉所有prod\_price至少为10的行，然后按照vend\_id分组数据；having子句过滤技术为2或2以上的分组；

再比如以age作为分组，计算相同age的行数，并输出行数>=0的age列：

![70][]

**3、分组和排序**

group by和order by的区别：

![983980-20170213161948738-1823289072.png][]

**order by的重要性：一般使用group by子句时，应该也给出order by子句，这是保证数据正确性的唯一方法（千万不要依赖group by排序数据）。**

**4、select子句顺序（从前往后）**

![983980-20170213162515066-2072528967.png][]

[70]: /images/20220524/af2f49ce31fd47549843a9f0e57993c0.png
[983980-20170213161948738-1823289072.png]: /images/20220524/9bac3b8473874f22a8e6eb3eb861a850.png
[983980-20170213162515066-2072528967.png]: /images/20220524/0f46bd8648dd41f8b61113eafa0be8fe.png