07- Mysql语法整理

最新推荐文章于 2025-06-18 23:10:12 发布

原创最新推荐文章于 2025-06-18 23:10:12 发布 · 157 阅读

CC 4.0 BY-SA版权

3 篇文章

订阅专栏

本文详细介绍如何使用SQL进行员工薪资的统计分析，包括总体薪资和、按部门和岗位分类的薪资总和、各岗位薪资排名及部门内薪资超过特定数额的情况。通过实际案例，展示SQL聚合函数、分组和子查询的运用。

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;