六、聚合函数和分组查询

最新推荐文章于 2022-04-29 14:56:31 发布

我与琳虽缘无分

最新推荐文章于 2022-04-29 14:56:31 发布

阅读量821

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/wnamej/article/details/105302508

版权

一、聚合函数简介
聚合函数能对集合中的一组数据进行计算，并返回单个计算结果；例如，计算满足各种条件的行数，找出某些字段的最小值和最大值，求某个字段的总和、平均值等；除COUNT(*)外，聚合函数均忽略空值。（包括：sum，max，min，avg，count等）
SUM函数：SUM函数返回选取的某列值的总和；

SELECT  SUM(Salary)  AS  月基本工资总额 FROM  Employee

MAX/MIN函数：MAX函数返回选取的某列的最大值；MIN函数返回选取的某列的最小值。

SELECT  MAX(Salary)  AS  最高月基本工资 FROM Employee
SELECT  MIN(Salary)  AS  最低月基本工资 FROM Employee
SELECT  MIN(Birthday)  AS  最高龄员工出生年月  FROM Employee

AVG函数：AVG函数返回选取的某列的平均值。

SELECT  AVG(Salary)  AS  月基本工资平均值  FROM  Employee

COUNT函数：
COUNT函数返回选取的某列或记录的行数；
COUNT(*)获取表行数；
COUNT(column)获取表中column列不为空的行数。

SELECT  COUNT(*)  AS  员工人数  FROM  Employee
--仅统计Birthday不为空的记录的数量。
SELECT  COUNT(Birthday)  AS  有出生记录的员工数 FROM  Employee

二、使用GROUP BY分组
GROUP BY意为“根据（by）一定的规则进行分组（Group）”；
其作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对若干个小区域进行统计汇总。
一般用于对查询结果分组统计，常与聚合函数联合使用。

--分组所依据的字段名称列表，可以为多个字段
GROUP  BY  字段列 [HAVING <条件表达式>]

案例一：
依据地区分组统计员工相关数据，统计员工数和员工月薪的平均值、最小值和最大值

select e.Address 地区, count(*) 员工数, Round(avg(e.salary),2) 平均月薪, sum(e.salary) 月薪总和,
 max(e.salary) 最高月薪, min(e.salary) 最低月薪from Employee egroup by Address order by 员工数

案例二：
统计不同部门的员工数量，按部门员工数量升序显示

select RankID 部门编号, count(*) 员工总数 from Employee group by RankID order by 员工总数

案例三：
统计非“武汉”地区所属员工的数量和员工月薪的平均值、最大值和最小值，并按照员工数升序显示查询结果

select e.Address 地区, count(*) 员工数, Round(avg(e.salary),2) 平均月薪, 
sum(e.salary) 月薪总和, max(e.salary) 最高月薪, min(e.salary) 最低月薪 
from Employee ewhere Address <> ‘武汉’ group by Address order by 员工数

注意：使用GROUP BY分组查询时，出现在SELECT子句中的非聚合函数列一定要出现在GROUP BY子句中。
三、多字段分组和HAVING子句
多字段分组规则：使用GROUP BY可以根据多个字段进行分组；
分组层次从左至右，即先按第一个字段分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组……依次类推。
**案例：**获取不同职级的员工在不同城市工作的人数，按员工职级升序排列：

SELECT  RankID  员工职级,  Address  员工工作地, COUNT(Address)  员工人数  
FROM  Employee  GROUP  BY  RankID,  Address   ORDER  BY  RankID
--先按照RankID进行分组，然后在RankID值相同的记录中，再根据Address进行分组。

使用HAVING对分组结果进行过滤
HAVING的含义和作用：
HAVING子句的作用是筛选满足条件的组，即在分组之后过滤数据；
使用 HAVING子句中的条件用于显示特定的组，条件中经常包含聚合函数；
HAVING子句在GROUP BY子句之后。
案例：查询每个职级的员工最高月基本工资大于10000的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列

SELECT  RankID  职级号 ,  MAX(Salary)   最高月基本工资 FROM  Employee   GROUP  BY  RankID 
 --先分组--再对分组结果进行过滤HAVING  MAX(Salary)>10000  ORDER BY  RankID

四、第六章的代码块：

- sum、max、min：

--工资总和：sum
select sum(salary) 工资总额 from employee
--最高工资：max；最低工资：min
select max(salary) 最高工资, min(salary) 最低工资 from employee
--显示所有技术人员的工资总额
select sum(salary) 工资总额 from employee where rankID like ‘%1’

- avg、count聚合函数

--所有员工的平均工资
select avg(salary) 平均工资 from employee
--职级为’1‘开头的员工的平均工资
select avg(salary) ’1‘员工平均工资 from employee where rankID like ‘%1’
--员工人数
select count(*) 员工人数 from employee
--salary不为空的员工人数
select count(salary) 定薪员工人数 from employeeselect count(salary) 
from employee where salary is not null

- group by分组统计

--统计每个地区的员工人数
select address 地区, count(*) 员工人数, sum(salary) 工资总额, 
avg(salary) 平均工资, max(salary) 最高工资, min(salary) 最低工资 
from employee group by address order by count(*), avg(salary)

- 多字段分组和having子句

--查询每个职级在不同地区的员工人数（优先级：左=》右）
select rankID 职级编号, address 地区, count(*)员工数 from employee group by 
rankID, address
--查询每个地区每个职级的员工人数
select address 地区, rankID 职级编号, count(*)员工数 from employee group by 
address, rankID order by address
--统计不同地区员工的平均工资
select address 地区, avg(salary) 平均工资 from employee group by address 
order by avg(salary)
--统计不同地区员工的平均工资，只显示平均工资大于12000的分组统计信息
select address 地区, avg(salary) 平均工资 from employee group by address 
having avg(salary) > 12000 order by avg(salary) 

--用于分组之后再加上一条汇总记录
select address 地区, count(*) 员工数 from employee group by address with rollup