MySQL之分组数据

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列：

 

3、分组和排序

group by和order by的区别：

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

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