mysql必知必会——GROUP BY和HAVING

青旅半醒 2022-05-29 06:18 146阅读 0赞

**mysql必知必会——GROUP BY和HAVING**

## 一、聚合函数       在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数， 例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。 ##

SELECT SUM(population) FROM bbc

## 这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即国家的总人口数。   二、GROUP BY用法       Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。    注意:group by 是先排序后分组；        举例子说明：如果要用到group by 一般用到的就是“每这个字段” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术 ##

select DepartmentID as '部门名称',
    COUNT(*) as '个数' from BasicDepartment group by DepartmentID

这个就是使用了group by +字段进行了分组，其中我们就可以理解为我们按照了部门的名称ID，DepartmentID将数据集进行了分组；然后再进行各个组的统计数据分别有多少；   
**    通俗一点说：group by 字段1，字段2。。。(整个表中不止这两个字段)表示数据集中字段1相等，字段2也相等的数据归为一组，只显示一条数据。那么你可以对字段3进行统计（求和，求平均值等）**  
注意

select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID

–将会出现错误  
      选择列表中的列 ‘DepartmentName’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。为什么呢，根据前面的说明：DepartmentID相等的数据归为一组，只显示一条记录，那如果数据集中有这样三条数据。  
      DepartmentID                              DepartmentName   
            dept001                                            技术部   
            dept001                                            综合部   
            dept001                                            人力部   
那我只能显示一条记录，我显示哪个？没法判断了。到这里有三种选择：  
1)、把DepartmentName也加入到分组条件里去（GROUP BY DepartmentID,DepartmentName），那这三条记录就是三个分组。  
2)、不显示DepartmentName字段。  
3)、用聚合函数把这三条记录整合成一条记录count(DepartmentName)

**三、WHERE和 HAVING  
    HAVING子句可以让我们筛选成组后的各组数据。HAVING子句可以使用聚合函数   
    WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前． WHERE字句中不能使用聚合函数 **  
    举例说明：   
    1）、显示每个地区的总人口数和总面积．

SELECT region, SUM(population), SUM(area)
    FROM bbc
    GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。  
    2）、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
    FROM bbc8 F4 w2 v( P- f
    GROUP BY region
    HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。相反，HAVING子句可以让我们筛选成组后的各组数据  
     **需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：   
    执行where子句查找符合条件的数据；   
    使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。   
    having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。   
    having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。**

三、GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

**select子句中的列名必须为分组列或列函数，列函数对于group by子句定义的每个组返回一个结果。**

某个员工信息表结构和数据如下：

id  name  dept  salary  edlevel        hiredate 
      1    张三  开发部 2000     3           2009-10-11
      2    李四  开发部 2500 	   3            2009-10-01
      3    王五  设计部 2600     5  	        2010-10-02
      4    王六  设计部 2300     4 	  	2010-10-03
      5    马七  设计部 2100     4 		2010-10-06
      6    赵八  销售部 3000     5 		2010-10-05
      7    钱九  销售部 3100     7 		2010-10-07
      8    孙十  销售部 3500     7 		2010-10-06

我想列出每个部门最高薪水的结果，sql语句如下：

select dept , max(salary) AS MAXIMUM
    FROM STAFF
    GROUP BY DEPT

查询结果如下：

dept       MAXIMUM
    开发部      4500
    设计部      2600
    销售部      3500

解释一下这个结果：

**1、 满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有group by中包含的列dept;**

**2、“列函数对于group by子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。**

四、将where子句与group by子句一起使用

**分组查询可以在形成组和计算列函数之前具有消除非限定行的标准where子句。必须在group by子句之前指定where子句**

例如，查询公司2010年入职的各个部门每个级别里的最高薪水

SELECT dept,edlevel,MAX(salary) AS MAXIMUM
    FROM STAFF
    WHERE hiredate > '2010-01-01'
    GROUP BY dept,edlevel

查询结果如下：