MYSQL基础上机练习题（五） GROUP BY子句应用

叁歲伎倆 2022-07-13 11:40 248阅读 0赞

### 一、实验目的 ###

GROUP BY子句应用

### 二、实验内容 ###

使用GROUP BY子句对[MYSQL基础上机练习题（二）][MYSQL]中的数据操作

### 三、题目 ###

1.  查询各部门人数
2.  查询各部门平均工资以及人数
3.  查询各部门不同性别的平均工资以及人数
4.  查询不同部门不同性别不同教育水平的平均工资情况

### 四、代码 ###

1.  查询各部门人数**（知识点：GROUP BY简单分组应用）**

SELECT DepartmentID, COUNT(*) AS '部门人数'
    FROM Employees
    GROUP BY DepartmentID

![Mysql GROUP BY应用][Mysql GROUP BY]  
2. 查询各部门平均工资以及人数**（知识点：GROUP BY在多表查询中的应用）**

SELECT DepartmentName AS '部门名称', COUNT(*) AS '部门人数', ROUND(AVG(InCome),2) AS '平均工资'
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY DepartmentID

![mysql GROUP BY应用][mysql GROUP BY]  
（ROUND函数是为了四舍五入，且显示两位小数）  
3. 产生一个结果集，包括各部门不同性别的平均工资以及人数**（知识点：GROUP BY聚合行、小计行、总计行生成（二维））**

SELECT DepartmentName AS '部门名称', COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY DepartmentID, Gender
    WITH ROLLUP

聚类维度为：  
（1）仅对部门分类（即小计行）：对五个部门的人数、平均工资进行统计  
（2）对部门中的不同性别员工分类（细分行）：对五个部门中的男、女不同性别进行统计  
![Mysql GROUP BY聚合][Mysql GROUP BY 1]  
4. 产生一个结果集，包括每个部门的男硕士的人数和平均工资、男本科的人数和平均工资、男大专的人数和平均工资、女硕士的人数和平均工资、女本科的人数和平均工资、女大专生的人数和平均工资、男员工人数和平均工资、女员工人数和平均工资、部门总人数和平均工资**（知识点：GROUP BY聚合行、小计行、总计行生成(三维））**

SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY DepartmentID, Gender, Education
    WITH ROLLUP

![Mysql][]  
在这种情况下，结果集的行数取决于维数和每个维度的值的个数：  
此题有(n=3)三维：Department(部门，部门值有5个），Gender(性别，性别值有两个），Educatioin(教育水平，教育水平值有三个)；  
因此，这里有(n+1=)4种分组：  
（1）汇总：全部的总计–所有员工的平均工资以及人数–(null,null,null)**共1行**  
（2）对Department分组：各个部门的员工人数以及平均工资–(DepartmentName, null, null)**共5行（DepartmentName有5个值）**  
（3）对每个Department中的不同性别值分组：各个部门中不同性别的员工的人数以及平均工资–（DepartmentName, Gender, null）**每个部门各2行，共5\*2=10行）**  
（4）对每个Department中的各个性别值中的不同教育水平分组：各个部门中各个性别的不同教育水平的员工的人数以及平均工资–（DepartmentName, Gender, Education）**（每个性别各3行，教育水平有3个值，共5\*2\*3=30行）**  
所以，结果集中最多可以有1+5+5\*2+5\*2\*3=46行  
且GROUP BY 后的属性前后排序不一样，结果集的行数不一样  
5. 产生一个结果集，分别根据部门、性别、教育水平对人数和平均工资进行聚合**（知识点：GROUP BY聚合行、交叉表、小计行、总计行生成(三维））**  
在SQL中，有CUBE这种聚合方式可以解决，但是Mysql不支持  
![Mysql 不支持CUBE][Mysql _CUBE]

只能联合六个表，即三个属性的前后排列组合得出的表进行联合后可以得出CUBE所可以得到的结果

/*排列组合1：部门、性别、教育*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY DepartmentID, Gender, Education
    WITH ROLLUP
    UNION
    /*排列组合2：性别、教育、部门*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY Gender, Education, DepartmentID
    WITH ROLLUP
    UNION
    /*排列组合3：教育、部门、性别*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY Education,DepartmentID, Gender
    WITH ROLLUP
    UNION
    /*排列组合4：部门、教育、性别*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY DepartmentID, Education, Gender
    WITH ROLLUP
    UNION
    /*排列组合5：教育、性别、部门*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY Education, Gender, DepartmentID
    WITH ROLLUP
    UNION
    /*排列组合6：性别、部门、教育*/
    SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, Education
    FROM Employees
    NATURAL JOIN Salary
    NATURAL JOIN Departments
    GROUP BY Gender, DepartmentID, Education
    WITH ROLLUP

在这种情况下，结果集的行数同样取决于维度数以及每个维度的值的个数，但是这种情况下的结果集行数更复杂一些：  
同样，此题有(n=3)三维：Department(部门，部门值有5个），Gender(性别，性别值有两个），Educatioin(教育水平，教育水平值有三个)；  
因此，这里有(2^n=)8种分组：  
（1）汇总：全部的总计–所有员工的平均工资以及人数–(null,null,null)**共1行**  
（2）对Department分组：各个部门的员工人数以及平均工资–(DepartmentName, null, null)**共5行（财务部、人力资源部、经理办公室、研发部、市场部）**  
（3）对Gender分组：男员工和女员工的人数以及平均工资–（Gender, null, null）**共2行**  
（4）对Education分组：硕士毕业、本科毕业、大专毕业的员工人数以及平均工资–（Education,null,null）**共3行**  
（5）对每个Department中的不同性别值分组：各个部门中男员工、女员工的人数以及平均工资–（DepartmentName, Gender, null）**每个部门各2行，共5\*2=10行）**  
（6）对每个Department中的不同教育水平值分组：各个部门中的硕士毕业、本科毕业、大专毕业的员工人数以及平均工资–（Department,Education,null）**每个部门各3行，共5\*3=15行**  
（7）对不同性别的员工的不同教育水平分组：男女员工中的硕士毕业、本科毕业、大专毕业的人数以及平均工资–（Gender,Education,null）**不同性别各3行，共2\*3=6行**  
（8）对每个Department中的各个性别值中的不同教育水平分组：各个部门中各个性别的不同教育水平的员工的人数以及平均工资–（DepartmentName, Gender, Education）**（每个性别各3行，教育水平有3个值，共5\*2\*3=30行）**  
所以，结果集中最多可以有1+5+2+3+5\*2+5\*3+2\*3+5\*2\*3=72行

[MYSQL]: http://blog.csdn.net/cheyennelam/article/details/53821210
[Mysql GROUP BY]: /images/20220713/f3dea03b70e44c7b8551256ac70eb3d8.png
[mysql GROUP BY]: /images/20220713/0ba9633c56024a479f9e2054a538b158.png
[Mysql GROUP BY 1]: /images/20220713/2591848258194a23a26b8efc03ce588e.png
[Mysql]: /images/20220713/2c4e2659e8a24fc3a0ab347dc82c0160.png
[Mysql _CUBE]: /images/20220713/3c6b8e2f742345d5af93d3037d4bcfa7.png