MYSQL基础上机练习题(五)

2017-01-08 08:20:27来源:CSDN作者:CheyenneLam人点击

一、实验目的

GROUP BY子句应用

二、实验内容

使用GROUP BY子句对MYSQL基础上机练习题(二)中的数据操作

三、题目

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

四、代码

  1. 查询各部门人数(知识点:GROUP BY简单分组应用)
SELECT DepartmentID, COUNT(*) AS '部门人数'FROM EmployeesGROUP BY DepartmentID

Mysql GROUP BY应用
2. 查询各部门平均工资以及人数(知识点:GROUP BY在多表查询中的应用)

SELECT DepartmentName AS '部门名称', COUNT(*) AS '部门人数', ROUND(AVG(InCome),2) AS '平均工资'FROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY DepartmentID

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

SELECT DepartmentName AS '部门名称', COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', GenderFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY DepartmentID, GenderWITH ROLLUP

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

SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY DepartmentID, Gender, EducationWITH 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

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

/*排列组合1:部门、性别、教育*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY DepartmentID, Gender, EducationWITH ROLLUPUNION/*排列组合2:性别、教育、部门*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY Gender, Education, DepartmentIDWITH ROLLUPUNION/*排列组合3:教育、部门、性别*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY Education,DepartmentID, GenderWITH ROLLUPUNION/*排列组合4:部门、教育、性别*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY DepartmentID, Education, GenderWITH ROLLUPUNION/*排列组合5:教育、性别、部门*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY Education, Gender, DepartmentIDWITH ROLLUPUNION/*排列组合6:性别、部门、教育*/SELECT DepartmentName, COUNT(*) AS '部门人数', AVG(InCome) AS '平均工资', Gender, EducationFROM EmployeesNATURAL JOIN SalaryNATURAL JOIN DepartmentsGROUP BY Gender, DepartmentID, EducationWITH 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行

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台