SQL - GROUP BY 和 HAVING

MySQL

GROUP BY

GROUP BY 简介

GROUP BY 语句根据字面上的意思为“根据（by）一定的规则进行分组（group）”。它的主要作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小的区域进行数据处理。其常常结合聚合函数（COUNT/SUM）等一起使用。

GROUP BY 语法

1
2
3
4
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

GROUP BY 使用

  1. GROUP BY [EXPERSSIONS]:

数据集根据表达式中的若干字段将一个数据集划分成不同的分组。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 以下例子为，统计某人在全国范围中购买的地产总数

SELECT owner.Name, COUNT(*) AS '购买总数' FROM registration 
LEFT JOIN owner ON owner.PersonID = registration.PersonID
GROUP BY registration.PersonID

+------+----------+
| Name | 购买总数 |
+------+----------+
| 小明 |        2 |
| 小红 |        1 |
| 小刚 |        2 |
+------+----------+

以上可以SQL语句可以解释为“按照房产登记表（regisration）中的身份证（PersonID）将数据集进行分组，然后按照分组来分别统计出各自的记录数量”。

  1. GROUP BY [EXPERSSIONS] WITH ROLLUP

指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。”按层结结构顺序“这段话是指按照 GROUP BY 语句中字段的顺序进行排序，比如 GROUG BY column1，column2， column3，那么这个分组的级别从高到低的顺序是 column1 > column2 > column3。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 以下例子为，统计全国各类型房产的销售面积情况

select ESTATE.EstateCity, ESTATE.EstateType, SUM(ESTATE.PropertyArea) AS '销售面积' FROM ESTATE  
LEFT JOIN REGISTRATION ON REGISTRATION.EstateID=ESTATE.EstateID 
GROUP BY ESTATE.EstateCity, ESTATE.EstateType WITH ROLLUP

+------------+------------+----------+
| EstateCity | EstateType | 销售面积 |
+------------+------------+----------+
| 北京市     | 住宅       |    71.00 |
| 北京市     | NULL       |    71.00 |
| 天津市     | 住宅       |    50.00 |
| 天津市     | NULL       |    50.00 |
| 惠州市     | 别墅       |   170.00 |
| 惠州市     | NULL       |   170.00 |
| 成都市     | 住宅       |    95.00 |
| 成都市     | 商铺       |   500.00 |
| 成都市     | NULL       |   595.00 |
| NULL       | NULL       |   886.00 |
+------------+------------+----------+

查询结果的第一句、第二句为WITH ROLLUP分析的第一组，可以解释为在北京市中，所有类型的房产总销售面积的总结。而查询结果中的最后一句解释为在全国中，所有类型的房产总销售面积的总结。

GROUP BY 语句容易出现的错误

通过以下语句利用 GROUP BY 进行查询的时候，会出现错误。

1
2
3
4
5
-- 利用城市进行分类返回查询到的所有数据

select ESTATE.EstateCity, *, SUM(ESTATE.PropertyArea) AS '销售面积' FROM ESTATE  
LEFT JOIN REGISTRATION ON REGISTRATION.EstateID=ESTATE.EstateID 
GROUP BY ESTATE.EstateCity

这是利用 GROUP BY 进行单表查询，抑或者多表查询时需要注意的点。在需要查询的字段中，即在返回集字段中，这些字段要么就要包含在 GROUP BY 后面，作为分组的依据。要么就要被包含在聚合函数（COUNT/SUM等）中。

不同平台出现的错误不同，如SQL SERVER返回的错误为：

1
2
3
> 消息 8120，级别 16，状态 1，第 1 行
> 选择列表中的列 'ESTATE.EstateID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
>

在MySQL中，返回的错误为：

1
2
3
> [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*, SUM(ESTATE.PropertyArea) AS '销售面积' FROM ESTATE  
> LEFT JOIN REGISTRAT' at line 1
>

HAVING

HAVING 简介

Having 和 GROUP BY 设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用。而 HAVING 搜索条件在进行分组操作之后应用。 HAVING 语法与 WHERE 语法类似，但 HAVING 中可以包含聚合函数并可以引用选择列表中显示的任一项。

HAVING 语法

1
2
3
4
5
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

HAVING 使用

HAVING 语句一般与GROUP BY搭配使用，没什么特别用法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 以下例子为，统计各地城市中各类住宅的总销售面积小于250平方米

select ESTATE.EstateCity, ESTATE.EstateType, SUM(ESTATE.PropertyArea) AS '销售面积' FROM ESTATE  
LEFT JOIN REGISTRATION ON REGISTRATION.EstateID=ESTATE.EstateID 
GROUP BY ESTATE.EstateCity, ESTATE.EstateType
Having SUM(ESTATE.PropertyArea) < 250

+------------+------------+----------+
| EstateCity | EstateType | 销售面积 |
+------------+------------+----------+
| 北京市     | 住宅       |    71.00 |
| 天津市     | 住宅       |    50.00 |
| 惠州市     | 别墅       |   170.00 |
| 成都市     | 住宅       |    95.00 |
+------------+------------+----------+

参考资料：

[1] http://www.cnblogs.com/glaivelee/archive/2010/11/19/1881381.html

[2]http://blog.csdn.net/qq_26562641/article/details/53301063