MySQL分组排序

前提数据准备：

emp数据表建表语句：

create table emp(
empno int,
ename varchar(40),
job varchar(40),
mgr int,
hiredate varchar(40),
sal double,
comm int,
deptno int
);

插入数据准备：

insert into emp values (7349,'clerk','engineer',7510,'1980-04-07',9100,null,10);
insert into emp values (7359,'smith','IT',7540,'1991-05-07',7500,500,10);
insert into emp values (7369,'will','develpoer',7270,'1996-12-07',6000,300,10);
 
insert into emp values (7500,'ron','engineer',7489,'1995-06-015',8500,null,20);
insert into emp values (7510,'john','salesman',7648,'1989-08-04',7200,500,20);
insert into emp values (7520,'fox','manager',7718,'1987-09-07',6000,300,20);

insert into emp values (7600,'earth','clerk',7716,'1991-10-07',9500,3000,30);
insert into emp values (7610,'zack','IT',7634,'1992-06-05',8800,1500,30);
insert into emp values (7620,'steven','engineer',7951,'1993-07-07',7500,null,30);

需求一： 哪些部门的薪水最高两位的工种有哪些？

1、分析：每个部门、每个工种的薪水和：

1、可以建立一个视图：
create view sal
as
select
deptno,job,sum(sal+ifnull(comm,0)) as sal
from emp group by deptno,job;

2、select * from sal；	视图中查询数据

• 视图不存储数据，生产上会把一些复杂的sql，多表的join结果作为视图来查询。

2、从1的结果集中找出薪水最高的工种有哪些？

两张表都是sal，一张表设为sal.a，另一张表设为sal.b；从结果集来看：这两张表是一样的

再插入一条数据后，再次进行SQL查询：

• insert into emp values (1000,‘BOSS’,‘BOSS’,NULL,‘1981-06-09’,9000,NULL,10);

select
a.*
from sal a
where (
select count(*) from sal b 
where a.deptno=b.deptno and a.sal < b.sal
) = 0;

• 部门编号是10，去b表找，a表的薪水<b表的薪水；继续第二行数据去找，a表的6300 < 20000、9100、8000，count数是3；所以要找到的就是count=0的数值，top1就是都不满足。

如果我们要取出top2的话，该怎么修改SQL?

select
a.*
from sal a
where (
select count(*) from sal b 
where a.deptno=b.deptno and a.sal < b.sal
) <= 1;

1、以部门升序，薪水降序

select
a.*
from sal a
where (
select count(*) from sal b 
where a.deptno=b.deptno and a.sal < b.sal
) <= 1
order by a.deptno,a.sal desc;