子查询和关联练习题，坑在子查询和开窗函数的使用

遇到的问题：
1.由于工位的机子上装的是mysql5.5版本，因此不支持开窗函数。第20题的查询无法进行，需要更换mysql8的版本。

--20.查询每个部门薪资最高的前两名员工信息及所在部门名称
select
	*
from
	(
	select 
		a.*,
		b.dname,
		/*使用开窗函数：对每组组内进行分区排序*/
		dense_rank() over (partition by deptno order by salary desc) as nb
	from
		emp as a
	join 
		dept as b
	on 
		a.deptno = b.deptno
	) a
where 
	a.nb < 3;

查询结果如下：

+-------+---------+-----------+---------+------------+--------+------+--------+------------+-------+
| empno | empname | job       | manager | hiredate   | salary | comm | deptno | dname      | rank1 |
+-------+---------+-----------+---------+------------+--------+------+--------+------------+-------+
|  7839 | king    | president |    NULL | 1981-11-17 |   5000 | NULL |     10 | accounting |     1 |
|  7782 | clark   | manager   |    7839 | 1981-06-09 |   2450 | NULL |     10 | accounting |     2 |
|  7788 | scott   | analyst   |    7566 | 1987-07-03 |   3000 | NULL |     20 | research   |     1 |
|  7902 | ford    | analyst   |    7566 | 1981-12-03 |   3000 | NULL |     20 | research   |     1 |
|  7566 | jones   | manager   |    7839 | 1981-04-02 |   2975 | NULL |     20 | research   |     2 |
|  7698 | blake   | manager   |    7839 | 1981-05-01 |   2850 | NULL |     30 | sales      |     1 |
|  7499 | allen   | salesman  |    7698 | 1981-02-20 |   1600 |  300 |     30 | sales      |     2 |
+-------+---------+-----------+---------+------------+--------+------+--------+------------+-------+

2.21题中不能使用子查询进行

--21.查询每个工资等级的员工个数并按照个数降序排序
select grade,count(*) from salgrade where ( select salary from emp)>losal and ( select salary from emp)<hisal group by grade;
--ERROR 1242 (21000): Subquery returns more than 1 row

以下是常用的查询语句练习，包含了表的创建以及要插入的数据。

--子表
create table emp(
	empno int primary key,
	empname varchar(10) not null,
	job varchar(10) not null,
	manager int,
	hiredate date,
	salary double,
	comm double,
	deptno int,
	constraint empkey foreign key (deptno) references dept(deptno)
);
--父表
create table dept(
	deptno int primary key,
	dname varchar(20) not null,
	loc varchar(20)
);
--工资等级表
create table salgrade(
	grade int primary key,
	losal double,
	hisal double
);

insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30); 
insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3000,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,null,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','clerk',7782,'1981-01-23',1300,null,10);

insert into dept values (10,'accounting','newyork');
insert into dept values (20,'research','dallas');  
insert into dept values (30,'sales','chicago');
insert into dept values (40,'operations','boston');  

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);


--6.查找部门编号为30的员工的详细信息
select * from emp where deptno = 30;
--7.查询从事CLARK工作的员工的编号、姓名、部门编号
select empno,empname,deptno from emp where job = 'clerk';
--8.查询奖金多于基本工资60%的员工信息
select * from emp where comm > salary*0.6;
--9.查询奖金少于100或者没有获得奖金的员工的信息
select * from emp where comm < 400 or comm is null;
--10.查询名字长度为6个字符的员工信息
select * from emp where char_length(empname) = 6;
--11.查询员工的信息，并按工作降序排序，如果工作相同按照工资升序排序
select * from emp order by job desc,salary asc;
--12.查询每个工作岗位的员工人数并按照人数降序排序
select count(*) as '人数',job as '工作' from emp group by job order by count(*) desc;
--13.查询每个工作岗位的最高薪资
select max(salary),job from emp group by job;
--14.查询所有员工的信息及所在部门名称
select a.*,b.dname from emp as a join dept as b on a.deptno = b.deptno;
--15.查询工资高于smith的员工信息
select * from emp where salary >(select salary from emp where empname = 'smith') ;
--16.查询销售部(sales)所有员工的姓名
select *from emp where deptno = (select deptno from dept where dname = 'sales');
--17.查询工资高于平均工资的员工信息
select *from emp where salary >(select avg(salary) from emp);
--18.查询与scott从事相同工作的员工信息
select * from emp where job =  (select job from emp where empname = 'scott');
--19.查询每个部门的最高薪资、最低薪资、平均薪资并按照最高薪资降序排序
select max(salary),min(salary),avg(salary),deptno from emp group by deptno order by max(salary) desc;
--20.查询每个部门薪资最高的前两名员工信息及所在部门名称
select
	*
from
	(
	select 
		a.*,
		b.dname,
		dense_rank() over (partition by deptno order by salary desc) as nb
	from
		emp as a
	join 
		dept as b
	on 
		a.deptno = b.deptno
	) a
where 
	a.nb < 3;
	
--这种方式错误
--select a.*,b.dname from emp a join dept b on a.deptno = b.deptno group by deptno order by salary desc limited 2;

--21.查询每个工资等级的员工个数并按照个数降序排序
--select grade,count(*) from salgrade where ( select salary from emp)>losal and ( select salary from emp)<hisal group by grade;
--ERROR 1242 (21000): Subquery returns more than 1 row
select 
	b.grade,
	count(a.empno)
from 
	emp a 
join 
	salgrade b 
on
	a.salary > b.losal and a.salary <hisal
group by
	b.grade
order by
	count(a.empno) desc;