你可以随时转身,但不能一直后退
- 取得每个部门最高薪水的人员名称
- 取得每个部门的最高薪水(按照部门编号分组)
- 在emp中找人员(谁的薪水等于最高薪水),1中的临时表t与emp表连接 条件是 e.deptno = t.deptno and t.maxsal = e.sal;(我写的时候没有前面的条件,说明我考虑连接条件的时候存在漏洞)
select
e.ename,t.*
from
emp e
join
(select deptno ,max(sal) maxsal from emp group by deptno) t
on
e.deptno = t.deptno and e.sal=t.maxsal;
- 哪些人的薪水在部门平均薪水之上
- 先找出每个部门的平均薪水(按照部门分组求平均薪水)
- 上查询结果作为 t表,与 emp表连接,连接条件是 e.deptno = t.deptno and e.sal > avgsal;
select
t.*,e.ename,e.sal
from
emp e
join
(select deptno,avg(sal) avgsal from emp group by deptno) t
on
e.sal > t.avgsal and e.deptno = t.deptno;
- 取得部门中(所有人的)平均的薪水等级
平均的薪水等级 :先计算每一个薪水的等级,找出薪水等级的平均值
平均薪水的等级:找出每个部门的平均薪水,再求等级
要注意group by 分组后,前面只能查询 group by 后面的字段 和 分组函数 ,它是有要求的!!!!
select
deptno,avg(grade) avggrade
from
emp
join
salgrade
on
sal between losal and hisal
group by
deptno;
- 不使用组函数max求得最高薪水(给出两种方案)
第一种:降序取第一个
第二种:用max
第三种:采用表的自连接,
select distinct e.sal from emp e join emp r on e.sal< r.sal ;
select
ename,sal
from
emp
where
sal not in (select distinct e.sal from emp e join emp r on e.sal< r.sal);
- 求取得平均薪水最高的部门的部门编号
方案一:
- 找出每个部门的平均薪水+编号
- 取得平均薪水的最大值 第一步降序取头部
方案二·- 找出每个部门的平均薪水+编号
- 通过max 求最大值
//方案一:
select
deptno,avg(sal)avgsal
from
emp
group by
deptno
order by
avgsal desc
limit 1;
//方案二:
select
deptno ,t.avgsal
from
(select deptno,avg(sal) avgsal from emp group by deptno) t
where
avgsal = (select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;
//同样是方案二:这是网课上老师的
select
deptno,avg(sal) avgsal
from
emp
group by
deptno
having
avgsal = (select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;
max(avgsal) 不可以的
6. 求取得平均薪水最高的部门的部门名称(根据第5题连接dept这个表得到部门名称)
select
d.DNAME
from
(select deptno,avg(sal) avgsal from emp group by deptno) t
join
dept d
on
t.deptno = d.deptno
where
avgsal =(select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;
- 求平均薪水的等级最低的部门的部门名称(第一个一定是平均薪水等级最低,但是第二个也有可能·,第三个也有可能,因为等级的判断是根据区间分的)
- 按照部门名称分组,找出每个部门的平均薪水
- 以上 t表和salgrade表连接,求得平均薪水等级
- 抛开之前找最低等级,升序找第一个,根据最低的平均薪水找最低等级
- 再通过grade = 最低等级;
//平均薪水最低的等级一定最低,但也存在平均薪水不是最低的等级最低
//鄙人的做法
select deptno,avg(sal) as avgsal from emp group by deptno;
select min(avgsal)as minavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno)t ;
select deptno from (select deptno,avg(sal) as avgsal from emp group by deptno)t where avgsal = 1566.666667;
select dname from dept where deptno = 30;
//来看看官方
select
t.dname,t.avgsal,s.grade
from
(select d.dname,d.deptno,avg(e.sal) as avgsal from dept d join emp e on d.deptno=e.deptno group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal
where
(select grade from salgrade s where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between s.losal and s.hisal) = s.grade;
- 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
- select distinct mgr from emp; 可以找出领导的员工编号,那么普通员工 就是 not in ( select distinct mgr from emp)
- 找出普通员工的最高薪水
- 找出高于 最高薪水的领导
select
ename,empno
from
emp
where
sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)) and empno in (select distinct mgr from emp where mgr is not NULL);
- 取得薪水最高的前五名员工
select
ename,sal
from
emp
order by
sal desc
limit
5;
- 取得薪水最高的第6到第10
select
ename,sal
from
emp
order by
sal desc
limit
5,5;
- 求最后入职的5名员工(日期也可以降序、升序)
select
empno,ename,hiredate
from
emp
order by
hiredate desc
limit 5;
- 求每个薪水等级有多少员工
- 找出每个员工的薪水等级
- 根据薪水等级分组,通过count 统计员工人数
select
s.grade,count(grade) as countGrade //因为后面有分组,所以这里只能有 分组字段 以及分组函数
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
s.grade;
- 列出所有员工及领导的姓名
select
e.ename '员工',m.ename '领导'
from
emp e
left join
emp m
on
e.mgr = m.empno;
- 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
自连接
select
e.ename '员工',e.hiredate, m.ename '领导',m.hiredate,d.dname
from
emp e
left join
emp m
on
e.mgr = m.empno
join
dept d
on
e.deptno = d.deptno
where
e.hiredate<m.hiredate;
- 列出部门名称和这些部门的员工信息,同时列出没有员工的部门
SELECT
e.*,
d.*
FROM
emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;
- 列出出少有5个员工的所有部门
我的想法太过复杂,明明可以直接连接dept表,然后再分组,而我居然将连接的表弄成一个中间表t 明显看起来很复杂
SELECT
dname,
count( *) count
FROM
emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY
d.dname
HAVING
count >= 5;
16. 列出薪资比”SMITH“多的所有员工信息
select
*
from
emp
where
sal > (select sal from emp where ename = 'SMITH');
- 列出比所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
每个部门的人数通过临时表
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
job = 'Clerk';
//每个部门人数
select deptno,count(*) deptcount from emp group by deptno;
//两张表联合起来
SELECT
t1.*,
t2.deptcount
FROM
(
SELECT
e.ename,
e.deptno,
d.dname
FROM
emp e
JOIN dept d ON e.deptno = d.deptno
WHERE
job = 'Clerk'
) t1
JOIN ( SELECT deptno, count(*) deptcount FROM emp GROUP BY deptno ) t2 ON t1.deptno = t2.deptno;
- 列出最低薪金大于1500的各种工作及从事此工作的所有雇员人数
同样可以先用where 将1500的过滤掉,**后面反应过来where 执行的时候还没有minsal呢!!!**所以用where 一开始过滤是不行的
SELECT
job,
min( sal ) AS minsal,
count(*) AS count //求从事此工作的所有雇员人数
FROM
emp
GROUP BY
job
HAVING
minsal > 1500;
- 列出部门在"SALES"(销售部)工作的员工的姓名,假定不知道销售部的部门编号
select deptno from dept where dname = 'SALES'; //找出销售部的部门编号
select
ename
from
emp
where
deptno = (select deptno from dept where dname = 'SALES');
- 列出薪金高于公司平均薪金的所有的员工,所在部门、上级领导、雇员的工资等级
SELECT
e.ename AS '员工',
d.dname AS '部门名称',
s.grade AS '工资等级',
m.ename '领导'
FROM
emp e
JOIN
dept d
ON
e.deptno = d.deptno
JOIN
salgrade s
ON
e.sal BETWEEN s.losal AND s.hisal
JOIN 、
emp m
ON
e.mgr = m.empno
WHERE
e.sal > ( SELECT avg( sal ) avgsal FROM emp );
- 列出与"SCOTT"从事相同工作的所有员工及部门名称
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno =d.deptno
where
job = (select job from emp where ename = 'scott') and ename != 'scott';
- 列出薪金等于部门30中员工的薪水的其他员工的姓名及员工(很奇怪)
SELECT DISTINCT
sal
FROM
emp
WHERE
deptno = 30;
SELECT
ename,
sal
FROM
emp
WHERE
sal IN ( SELECT ename, deptno, sal FROM emp WHERE deptno = 30 )
AND deptno != 30;
- 列出薪金高于部门30中员工的薪水的其他员工的姓名及员工
select max(sal) from emp where deptno = 30;
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.sal> (select max(sal) from emp where deptno = 30)and e.deptno!=30;
- 列出每个部门工作的员工员工数量、平均工资和平均服务期限
注意:40部门没有一个员工,请问如何处理
平均服务期限 :系统当前年份 - 入职年份,TimeStampDiff(间隔类型,前一个日期,后一个日期)
间隔类型:年,月,日,季度,星期
select
d.deptno,count(e.ename) as '员工数量',ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(year,e.hiredate,now())),0)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;
- 列出所有员工的姓名、部门名称和工资
select
e.ename ,d.dname ,e.sal
from
emp e
join
dept d
on
e.deptno = d.deptno;
- 列出所有部门的详细信息和人数
SELECT
d.*,
t.`员工数量`
FROM
dept d
LEFT JOIN ( SELECT deptno, count( ename ) AS '员工数量', avg( sal ) AS '平均工资' FROM emp e GROUP BY deptno ) t ON d.deptno = t.deptno;
- 列出各种工作的最低工资及从事此工作的雇员的信息
对于如何找员工有点奇怪
SELECT
e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
FROM
emp e
JOIN ( SELECT job, min( sal ) minsal FROM emp GROUP BY job ) t ON e.sal = t.minsal;
- 列出每个部门的MANAGER (领导)的最低薪金(?????)
- 列出所有员工的 年工资 按年薪从低到高排列
select
ename,(sal+ifnull(comm,0))*12 income
from
emp
order by
income asc;
- 求员工领导的薪水超过3000的员工名称与领导名称
select e.ename '员工',m.ename '领导' from emp e left join emp m on e.mgr = m.empno where m.sal>3000;
31. 求出部门名称中带"S"字符的部门员工的工资合计、部门人数(为啥 right join 它是错的 )
加where 条件 ,不用having
而且一定要考虑分组函数那什么自动忽略NULL,且所有运算和null做运算都是NULL,并且补充一点,如果某个值要等于NULL,不能直接 = NULL ,要 is NULL;
SELECT
d.deptno,
d.dname,
d.loc,
ifnull( sum( e.sal ), 0 ),
count( e.ename )
FROM
emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
WHERE
d.dname LIKE '%s%'
GROUP BY
d.deptno,
d.dname,
d.loc;
- 给任职日期超过30年的员工加薪10%
update emp set sal = sal *1.1 where timestampdiff(year,hiredate,now());