/*算出员工的平均工资*/ select e.*,avg(e.salary) salarysum from employee e group by e.departmentNo; /*根据部门编号分组，列出部门名称，部门编号，部门人数，部门内最大薪水，部门内最小薪水，部门内薪水差，部门内平均工资*/ select d.departmentName,e.departmentNo,count(e.employeeId) employeeNum,max(e.salary) maxsalary,min(e.salary) minsalary,max(e.salary)-min(e.salary) salarydiff,avg(e.salary) avgsalary from department d,employee e where e.departmentNo=d.departmentNo group by e.departmentNo; /*列出员工编号，员工姓名，员工薪水，公司平均工资，员工工资与平均工资差别，评语*/ select e.employeeNo,e.employeeName,e.salary,(select avg(salary) from employee) comavgsalary,e.salary-(select avg(salary) from employee) salarydiff, case when abs(e.salary-(select avg(salary) from employee)) >300 then 'big diff' when abs(e.salary-(select avg(salary) from employee)) >=100 then 'a little diff' when abs(e.salary-(select avg(salary) from employee)) <100 then 'almost ignore' end classify from employee e /*查询出员工的薪水，将其与部门的平均薪水、公司的平均薪水作比较并作出评语。*/ select e.salary, (select avg(salary) from employee where departmentNo = e.departmentNo) deptavg, (select avg(salary) from employee) comavg, e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo) deptdiff, e.salary-(select avg(salary) from employee) companydiff, case when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) >300 then 'big diff' when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) >=100 then 'a little diff' when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) <100 then 'almost ignore' end comparewithdept, case when abs(e.salary-(select avg(salary) from employee)) >300 then 'big diff' when abs(e.salary-(select avg(salary) from employee)) >=100 then 'a little diff' when abs(e.salary-(select avg(salary) from employee)) <100 then 'almost ignore' end comparewithcom from employee e /*员工编号，员工名称，部门编号，部门平均工资，部门最大工资，部门最小工资，部门内工资差*/ select e.employeeNo,e.employeeName,e.departmentNo,e.salary,(select avg(salary) from employee where departmentNo = e.departmentNo) deptavg,(select max(salary) from employee where departmentNo = e.departmentNo) maxsalary, (select min(salary) from employee where departmentNo = e.departmentNo) minsalary,(select max(salary)-min(salary) from employee where departmentNo = e.departmentNo) diffwithmaxandmin from employee e order by diffwithmaxandmin desc; /*假如有个好心的老板要消除两极分化，对大于平均工资的乘以0.95，小于平均工资的乘以1.05，等于的不调整。*/ SELECT e.employeeNo,e.employeeName,e.salary, case when e.salary>(select avg(salary) from employee) then e.salary*0.95 when e.salary=(select avg(salary) from employee) then e.salary when e.salary<(select avg(salary) from employee) then e.salary*1.05 end adjustsalary FROM employee e; /*查询部门编号最大部门的员工信息*/ select e.employeeNo,e.employeeName,e.departmentNo,e.salary FROM employee e where e.departmentNo in(select max(departmentNo) from department); /*剔除部门编号最大和最小的员工信息*/ SELECT e.employeeNo,e.employeeName,e.departmentNo,e.salary FROM employee e where e.departmentNo not in ((select max(departmentNo) from department),(select min(departmentNo) from department)); /*列出和平均工资相差超过300的员工信息*/ select e.employeeNo,e.employeeName,e.departmentNo,e.salary FROM employee e where abs(e.salary-(select avg(salary) from employee))>300 /*列出平均工资大于4500的分组*/ select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary FROM employee e group by e.departmentNo having avg(e.salary)>4500 /*按多字段分组*/ select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary FROM employee e group by e.departmentNo,e.employeeNo /*聚合统计，cube生成的结果显示了所选列中值的所有组合的聚合中，rollup生成的结果集显示了所选列中值的某个层次结构的聚合*/ select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary FROM employee e where e.salary>4500 group by e.departmentNo with rollup /*分组查询的排序，求出每个部门内的最大、最小工资及极差。按照极差的倒序排序*/ select e.departmentNo,max(e.salary) maxsalary,min(e.salary) minsalary,(max(e.salary)-min(e.salary)) diff FROM employee e group by e.departmentNo order by diff desc /*compute by MySQL不支持该语法*/ select e.employeeNo,e.employeeName,e.salary from employee e order by e.salary compute avg(e.salary),max(e.salary),min(e.salary) by e.salary /*区间查询，在between之前加一个not 可以反向选择*/ select * from employee where salary between (select min(salary) from employee where departmentNo = '1474') and (select MAX(salary) from employee where departmentNo='1915') /*多表嵌套子查询*/ select * from employee where salary > (select avg(salary) from employee where departmentNo in (select departmentNo from department where departmentNo like '%18%')) /*查询人数最多的部门名称，用了虚拟表查询。觉得太复杂了。可以简化一下就好了*/ select departmentName from department where departmentNo = (select departmentNo from (select departmentNo,count(*) employeeNum from employee group by departmentNo)temp where employeeNum = (select max(employeeNum) from (select departmentNo,count(*) employeeNum from employee group by departmentNo)temp)) /*判断是否存在薪水在4000-4200之间的员工。返回的结果不重要*/ select * from employee where exists (select * from employee where salary between 4000 and 4200) /*人话说一遍，列出薪水比部门编号为1296中任一员工高的信息。等价于列出比部门编号为1296最低工资高的员工信息,等价于 select * from employee where salary > (select min(salary) from employee where departmentNo ='1296');*/ select * from employee where salary > any(select salary from employee where departmentNo='1296'); /*改一下关键字any -> all 就等价于比部门编号为1296最高工资高的员工信息了。原意是比部门编号为1296所有员工工资都要高的员工信息。*/ select * from employee where salary > all(select salary from employee where departmentNo='1296') /*列出大于所有部门的平均工资的员工信息。省了去找最高部门平均工资的步骤。换成any好像就没什么意义了*/ select * from employee where salary > all(select avg(salary) from employee group by departmentNo) /*列出各个部门最高工资员工的信息*/ select e.* from employee e where e.salary in (select max(salary) from employee where departmentNo = e.departmentNo )
