select
ename as 员工姓名,
sal as 月薪
from tb_emp where sal=(select max(sal) from tb_emp);
-- 或者
select ename, sal from tb_emp where sal>=all(select sal from tb_emp);
*查询员工的姓名和年薪((月薪+补贴)13)
select
ename as 员工姓名,
(ifnull(comm, 0) + sal)*13 as 年薪
from tb_emp
order by 年薪 desc;
查询有员工的部门的编号和人数
select
dno as 部门编号,
count(*) as 人数
from tb_emp
group by dno;
查询所有部门名称和人数
select
dname as 部门名称,
ifnull(total, 0) as 人数
from tb_dept left join(
select dno, count(*) as total from tb_emp group by dno
) tb_temp on tb_dept.dno = tb_temp.dno;
查询月薪最高的员工(Boss除外)的姓名和月薪
select
ename as 员工姓名,
sal as 月薪
from tb_emp
order by sal desc limit 1,1;
-- 或者
select ename, sal from tb_emp
where sal=(
select max(sal) from tb_emp where mgr is not null
);
查询月薪排第2名的员工的姓名和月薪
select ename, sal from tb_emp where sal=(
select distinct sal from tb_emp order by sal desc limit 1,1
);
select ename, sal from tb_emp where sal=(
select max(sal) from tb_emp where sal<(select max(sal) from tb_emp)
);
查询月薪排第N名的员工的姓名和月薪
查询月薪超过平均月薪的员工的姓名和月薪
select
ename as 员工姓名,
sal as 月薪
from tb_emp
where sal > (
select avg(sal) from tb_emp
);
查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename, t1.dno, sal from tb_emp t1 inner join(
select dno, avg(sal) as avg_sal from tb_emp group by dno
) t2 on t1.dno=t2.dno and sal>avg_sal;
查询部门中月薪最高的人姓名、月薪和所在部门名称
select ename, sal, dname
from tb_emp t1, tb_dept t2, (
select dno, max
(sal) as max_sal from tb_emp group by dno
) t3 where t1.dno=t2.dno and t1.dno=t3.dno and sal=max_sal;
查询主管的姓名和职位
提示,尽量少用in和not in运算,尽量少用distinct操作
可以使用存在性判断(exists/ not exists)替代集合运算和去重操作
select ename, job from tb_emp where eno in(
select distinct mgr from tb_emp where mgr is not null
);
select ename, job from tb_emp where eno=any(
select distinct mgr from tb_emp where mgr is not null
);
-- 提示,尽量少用in 和 not in 运算,尽量少用distinct 操作
-- 可以使用存在性判断(exists / not exists)替代集合运算和去重操作
select ename, job from tb_emp t1 where exists(
select 'x' from tb_emp t2 where t1.eno=t2.mgr
);
查询月薪排名4~6名的员工的排名、姓名和月薪
select ename, sal from tb_emp order by sal desc limit 3,3;
select row_num, ename, sal from
(select @a:=@a+1 as row_num, ename, sal
from tb_emp, (select @a:=0) t1 order by sal desc) t2
where row_num between 4 and 6;
窗口函数
MySQL8有窗口函数:row_number() / rank() / dense_rank()
-- 窗口函数不适合业务数据库,只适合做离线数据分析
select ename, sal,
row_number() over (order by sal desc) as row_num,
rank() over (order by sal desc) as ranking,
dense_rank() over (order by sal desc) as dense_ranking
from tb_emp limit 3 offset 3;
select ename, sal, ranking from(
select ename, sal, dense_rank() over (order by sal desc) as ranking
from tb_emp
) tb_emp where ranking between 4 and 6;
窗口函数主要用于解决TopN查询问题
查询每个部门月薪排前两名的员工姓名和月薪
select ename, sal, dno from(
select ename, sal, dno, rank() over (partition by dno order by sal desc) as ranking
from tb_emp
) tb_temp where ranking<=2;
MySQL5.7 不支持窗口函数的写法
select ename, sal, dno from tb_emp t1
where(
select count(*) from tb_emp t2
where t1.dno=t2.dno and t2.sal > t1.sal
) < 2 order by dno asc, sal desc;