07- Mysql语法整理

1、求员工表所有人的薪水和

select
sum(sal) as salsum
from emp;

2、求每个部门的所有人的薪水和

select
deptno,
sum(sal) as salsum
from emp
group by deptno;

3、求每个部门的每个岗位的所有人的薪水和

select 
deptno,job
sum(sal) as salsum
from  emp
group by deptno,job;

4、求每个部门每个岗位的所有人的薪水和人数

select 
deptno,job
sum(sal) as salsum
count(deptno) as pnum
from emp
group by deptno,job;

5、求薪水和大于1500的部门

select 
deptno,
sum(sal) as salsum
from emp
group by deptno
having sum(sal)>1500;

# 方法2 子查询
select 
t.*
from
(
select 
deptno,
sum(sal) as salsum
from emp
group by deptno) as t
where t.salsum>1500;

6、每个部门的薪水最高两位的工种？重要！！！

create view sal 
as
select
deptno,job,sum(sal+ifnull(comm,0)) as sal
from emp group by deptno,job;
--- comm是薪水，里面有null值

2. 从1结果集找出薪水最高的工种有哪些(top1)？

--- 两张表都是sal
select 
a.*
from sal a 
where 
(
  select count(*) from sal b where a.deptno = b.deptno and a.sal <b.sal
) =0
order by a.deptno;

3. 从1结果中找出top2

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 asc, a.sal desc;