注:如果我的方法旁边写了同,表明和答案的方法是一样的,
1、取得每个部门最高薪水的人员名称
@@@@@@@@
我的思考:
法一同
1.找出每个部门的最高薪水:
mysql> select deptno, max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.01 sec)
2.由上面的信息得到人员名字:
mysql> select deptno, max(sal),ename from emp group by deptno;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
//当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段,所以不能有ename。
因为想找ename要用到1中的表同时还要用到别的表(emp表),所以可以将1中的表当作临时表t,用子查询。
mysql> select t.*,e.ename from (select deptno, max(sal) maxsal from emp group by deptno) t join emp e on t.maxsal=e.sal;
+--------+---------+-------+
| deptno | maxsal | ename |
+--------+---------+-------+
| 30 | 2850.00 | BLAKE |
| 20 | 3000.00 | SCOTT |
| 10 | 5000.00 | KING |
| 20 | 3000.00 | FORD |
+--------+---------+-------+
4 rows in set (0.00 sec)
我觉得这样还是有点不严谨,因为只用maxsal和sal对比的话,如:2850是部门30的maxsal,但万一也是部门20中的小张的薪资,则可能会把小张也查出来,但是小张并不是其部门的最高薪资。
所以我觉得要再核对一下部门编号。修改如下:
mysql> select t.*,e.ename from (select deptno, max(sal) maxsal from emp group by deptno) t join emp e on t.maxsal=e.sal where e.deptno=t.deptno;
+--------+---------+-------+
| deptno | maxsal | ename |
+--------+---------+-------+
| 30 | 2850.00 | BLAKE |
| 20 | 3000.00 | SCOTT |
| 10 | 5000.00 | KING |
| 20 | 3000.00 | FORD |
+--------+---------+-------+
4 rows in set (0.00 sec)
(我的考虑是对的,但是我用了on和where两个筛选来分别放薪资和部门的条件。答案是直接用and将两个条件相连放在on中,没用where)
@@@@@@@@
第一步:取得每个部门最高薪水(按照部门编号分组,找出每一组最大值)
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal |
+--------+---------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+---------+
第二步:将以上的查询结果当做一张临时表t,
t和emp表连接,条件:t.deptno = e.deptno and t.maxsal = e.sal
select
e.ename, t.*
from
emp e
join
(select deptno,max(sal) as maxsal from emp group by deptno) t
on
t.deptno = e.deptno and t.maxsal = e.sal;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
2、哪些人的薪水在部门的平均薪水之上
@@@@@@@@
我的思考:
法一同
1找出部门平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2找出每个部门里高于该部门平均薪水的人
select e.ename,e.sal,t.*
from emp e
join (select deptno,avg(sal) avgsal from emp group by deptno) t
on e.deptno=t.deptno and e.sal>t.avgsal;
+-------+---------+--------+-------------+
| ename | sal | deptno | avgsal |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 | 30 | 1566.666667 |
| JONES | 2975.00 | 20 | 2175.000000 |
| BLAKE | 2850.00 | 30 | 1566.666667 |
| SCOTT | 3000.00 | 20 | 2175.000000 |
| KING | 5000.00 | 10 | 2916.666667 |
| FORD | 3000.00 | 20 | 2175.000000 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)
成功,和第一题一样的思路
@@@@@@@@
第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:将以上查询结果当做t表,t和emp表连接
条件:部门编号相同,并且emp的sal大于t表的avgsal
select
t.*, e.ename, e.sal
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
e.deptno = t.deptno and e.sal > t.avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal | ename | sal |
+--------+-------------+-------+---------+
| 30 | 1566.666667 | ALLEN | 1600.00 |
| 20 | 2175.000000 | JONES | 2975.00 |
| 30 | 1566.666667 | BLAKE | 2850.00 |
| 20 | 2175.000000 | SCOTT | 3000.00 |
| 10 | 2916.666667 | KING | 5000.00 |
| 20 | 2175.000000 | FORD | 3000.00 |
+--------+-------------+-------+---------+
3、取得部门中(所有人的)平均的薪水等级
@@@@@@@@
我的思考:
题目意思是:求每个部门的员工的薪水等级的平均值
法一同
1求每个人的薪水等级
select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+--------+-------+
| ename | deptno | grade |
+--------+--------+-------+
| SMITH | 20 | 1 |
| ALLEN | 30 | 3 |
| WARD | 30 | 2 |
| JONES | 20 | 4 |
| MARTIN | 30 | 2 |
| BLAKE | 30 | 4 |
| CLARK | 10 | 4 |
| SCOTT | 20 | 4 |
| KING | 10 | 5 |
| TURNER | 30 | 3 |
| ADAMS | 20 | 1 |
| JAMES | 30 | 1 |
| FORD | 20 | 4 |
| MILLER | 10 | 2 |
+--------+--------+-------+
14 rows in set (0.00 sec)
2 按照部门分组,找出等级的平均值,这里不需要用临时表,因为我们要的信息就在上面那张表上,和其他表无关了,只用当作普通的查询即可。对上表分组求均值即可
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
成功
@@@@@@@@
平均的薪水等级:先计算每一个薪水的等级,然后找出薪水等级的平均值。
平均薪水的等级:先计算平均薪水,然后找出每个平均薪水的等级值。
第一步:找出每个人的薪水等级
emp e和salgrade s表连接。
连接条件:e.sal between s.losal and s.hisal
select
e.ename,e.sal,e.deptno,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| CLARK | 2450.00 | 10 | 4 |
| KING | 5000.00 | 10 | 5 |
| MILLER | 1300.00 | 10 | 2 |
| SMITH | 800.00 | 20 | 1 |
| ADAMS | 1100.00 | 20 | 1 |
| SCOTT | 3000.00 | 20 | 4 |
| FORD | 3000.00 | 20 | 4 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| TURNER | 1500.00 | 30 | 3 |
| BLAKE | 2850.00 | 30 | 4 |
| ALLEN | 1600.00 | 30 | 3 |
| JAMES | 950.00 | 30 | 1 |
| WARD | 1250.00 | 30 | 2 |
+--------+---------+--------+-------+
第二步:基于以上的结果继续按照deptno分组,求grade的平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
4、不准用组函数(Max ),取得最高薪水;
@@@@@@@@
我的思考:
法一同
将所有人的sal按照降序排列,第一行的就是最高sal
select * from emp order by sal desc limit 0,1;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
成功(不过用这种方法有个缺点:如果有好几个都是5000,那你就不能这样只取第一行的数据了)
我再改进下:
select * from emp where sal=(select sal from emp order by sal desc limit 0,1);
这样就解决了上面的问题。成功。
但除了排序我没想到别的办法,然后我看答案还可以用表的自连接,我先试试:
select a.*
from emp a
join emp b
on a.sal<b.sal;
这样会输出除了最高工资的KING以外的所有人的名字并且会重复输出,如果是on a.sal<b.sal;则会输出除了最低工资的smith以外的人的名字,
如果找到上面表不存在的人就能得到KING。但我不知道怎么用自连接只输出KING
我没想出来,看答案,其实还用了子查询,不过我也没想到distinct和not in
@@@@@@@@
第一种:sal降序,limit 1
((不过用这种方法有个缺点:如果有好几个都是5000,那你就不能这样只取第一行的数据了)我的方法再次基础上改进了一下)
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
第二种方案:select max(sal) from emp;
第三种方案:表的自连接
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
(我没想起distinct和not in)
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
a表
+---------+
| sal |
+---------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+---------+
b表
+---------+
| sal |
+---------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+---------+
5、取得平均薪水最高的部门的部门编号;
@@@@@@@@
我的思考:
法一:同
1.按照部门分组得到各部门平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.按照avgsal降序排,取第一行的:
select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 0,1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
成功,当然也可以想上一题我的思考中那样再完善下“有多个最高”的情况,在此我不写了。
法二:max:
select deptno,max(avg(sal)) from emp group by deptno:
ERROR 1111 (HY000): Invalid use of group function
注意:出错了,分组函数1(分组函数2()),mysql中不可以这样使用,而Oracle中可以这样使用
mysql> select max(avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) ;
ERROR 1248 (42000): Every derived table must have its own alias
注意:仍然有问题!必须要像下面写的这样重命名一下才对!!!别忘了!!
select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+---------------+
| max(t.avgsal) |
+---------------+
| 2916.666667 |
+---------------+
1 row in set (0.00 sec)
还要加上对应的部门名字,于是我多加一个字段t.deptno:
select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
“取最大平均薪水,这个时候没有用字段进行分组,所以只能使用分组函数,而取不出来deptno”
但此时竟然报错了,进行下面实验:
mysql> select t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+-------------+
| avgsal |
+-------------+
| 2175.000000 |
| 1566.666667 |
| 2916.666667 |
+-------------+
3 rows in set (0.00 sec)
mysql> select t.deptno from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
| 10 |
+--------+
mysql>select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
没有group by的情况下,查单独字段没问题,查单独分组函数也没问题,两个放在一起以后就出问题了,
错误 1140 (42000):在没有 GROUP BY 的聚合查询中,SELECT 列表的表达式 #1 包含非聚合列“t.deptno”;这与 sql_mode=only_full_group_by 不兼容
那我试试如果是正常的表,不是临时表是否可以:
select e.ename,max(e.sal) from emp e;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'bjpowernode.e.ENAME'; this is incompatible with sql_mode=only_full_group_by
哦,同样是不可以的!那我怎么印象中之前学的时候是写过类似上面这行命令的?我找一找,确实没找到想这里一样写的,只找到唯一一个类似的也是写了group by的,3.3、from后面嵌套子查询中的(select avg(t.grade),deptno from (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno;),不过这个句子是我自己写的,其实当时那里根本不用“临时表”。
加入group by试试
select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t group by t.deptno;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+---------------+
3 rows in set (0.01 sec)
奇怪,应该只显示一条记录啊,它这个和临时表t的结果一模一样
我想了想,这样确实不对,t.deptno有3个数据,而max(t.avgsal)只有一个数据,如果没有筛选条件让t.deptno只留下一个的话是无法形成表格的
我再试验一下,对正常的表添加一个筛选语句试试:
mysql> select e.ename,max(e.sal) from emp e where ename='smith';
+-------+------------+
| ename | max(e.sal) |
+-------+------------+
| SMITH | 800.00 |
+-------+------------+
1 row in set (0.00 sec)
没有报错,但是数据并不正确,输出的是Smith的sal,max函数没有起作用。因为是先执行where再分组再分组函数的,执行了where以后其实只有一条smith相关的数据在表格中了,此时smith的sal800就是表格中的最大数据了。所以我们看到了上面的输出结果。试试having:
select t.deptno,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t having t.avgsal=max(t.avgsal);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 't.deptno'; this is incompatible with sql_mode=only_full_group_by
也不行。
于是我明白了,要遵循这样的规则:
1.没有group by的情况下,可以单独查字段,也可以单独查分组函数,但是不能同时查字段和分组函数。
2.如果同时查字段和分组函数,则要求必须group by要查的字段。(也就是之前说如果有group by则select里面只能写用来分组的字段和分组函数)
我重新写一下:
之前已经得到这个结果了;
select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t ;
+---------------+
| max(t.avgsal) |
+---------------+
| 2916.666667 |
+---------------+
1 row in set (0.00 sec)
select t.deptno,t.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t where t.avgsal=(select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------
结果是对的,但是不好,太复杂了,看答案
@@@@@@@@
第一种方案:降序取第一个。
第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:降序选第一个。
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
第二种方案:max
第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:找出以上结果中avgsal最大的值。
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
| 2916.666667 |
+---------------+
第三步:
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
6、取得平均薪水最高的部门的部门名称;
@@@@@@@@
我的思考:
同
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno =d.deptno
group by dname
order by avgsal desc
limit 0,1;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.01 sec)
@@@@@@@@
select
d.dname,avg(e.sal) as avgsal
from
emp e
join
dept d
on
e.deptno = d.deptno
group by
d.dname
order by
avgsal desc
limit
1;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
7、求平均薪水的等级最低的部门的部门名称;
@@@@@@@@
我的思考:
法一:只适用于只有一个最低值
1.按照部门分组,求每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.求出平均薪水的“等级“
select t.*,s.grade
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
3.limit排序取出等级最低的(只适用于只有一个最低值)
select t.*,s.grade
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal
order by s.grade
limit 0,1;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 30 | 1566.666667 | 3 |
+--------+-------------+-------+
1 row in set (0.00 sec)
4.join dept表显示部门名称
select t.*,s.grade,d.dname
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal
join dept d
on t.deptno=d.deptno
order by s.grade
limit 0,1 ;
+--------+-------------+-------+-------+
| deptno | avgsal | grade | dname |
+--------+-------------+-------+-------+
| 30 | 1566.666667 | 3 | SALES |
+--------+-------------+-------+-------+
1 row in set (0.01 sec)
成功,但是用limit这样做只针对只有一条最低记录的情况,如果最低记录有多条就不能这样了。
法二:要求考虑最低记录有多条的情况
1.2.还是同法一一样
1.按照部门分组,求每个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
2.求出平均薪水的“等级“
select t.*,s.grade
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
3.加入显示部门名字(就是法一的4,但没加limit和order by )
select t.*,s.grade,d.dname
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal
join dept d
on t.deptno=d.deptno ;
+--------+-------------+-------+------------+
| deptno | avgsal | grade | dname |
+--------+-------------+-------+------------+
| 30 | 1566.666667 | 3 | SALES |
| 20 | 2175.000000 | 4 | RESEARCH |
| 10 | 2916.666667 | 4 | ACCOUNTING |
+--------+-------------+-------+------------+
3 rows in set (0.00 sec)
4.考虑怎么把所有的最低记录找出来
select t.*,s.grade,d.dname
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal
join dept d
on t.deptno=d.deptno
where s.grade = (select min(grade) from 这张表) 我觉得这样很麻烦,就没写了。看答案
@@@@@@@@
平均薪水是800
平均薪水是900
那么他俩都是1级别。
第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:找出每个部门的平均薪水的等级
以上t表和salgrade表连接,条件:t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+------------+-------------+-------+
| dname | avgsal | grade |
+------------+-------------+-------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
+------------+-------------+-------+
3.找出最低的薪资等级,然后将所有等于这个“最低薪资等级“的都筛出来
平均薪水最低的对应的等级一定是最低的.
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal |
+-------------+
| 1566.666667 |
+-------------+
select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
+-------+
| grade |
+-------+
| 3 |
+-------+
根据3来筛
select
t.*,s.grade
from
(select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal
where
s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名;
@@@@@@@@
我的思考
先把所有普通员工都找出来,然后找到普通员工的最高薪水maxsal,找出所有不在普通元表中的并且工资大于maxsal的人即可
mysql> select ename,sal,empno from emp where empno not in mgr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mgr' at line 1
不可以这样写,not in(特别注意not in结果集中不能有null,如果not in结果集中有Null,则查询结果直接为Null)不能直接跟这种字段呀,它和>,=,<这些一样,跟的是一个值,所以我们可以用select将mgr都选出来,而不是直接写这个字段:
select ename,sal,empno from emp where empno not in (select mgr from emp);
Empty set (0.00 sec)//得到的是一个空集合,为什么呢?因为mgr中有NULL,而对于not in来说,只要有NULL,查询结果直接为NULL
所以我要换个思路,不查普通员工了,查领导,得到所有领导的名字:
select ename,sal,empno from emp where empno in (select mgr from emp);
+-------+---------+-------+
| ename | sal | empno |
+-------+---------+-------+
| FORD | 3000.00 | 7902 |
| BLAKE | 2850.00 | 7698 |
| KING | 5000.00 | 7839 |
| JONES | 2975.00 | 7566 |
| SCOTT | 3000.00 | 7788 |
| CLARK | 2450.00 | 7782 |
+-------+---------+-------+
6 rows in set (0.00 sec)
现在要找出普通员工的最高薪水:
select max(e.sal) from emp e join (select ename,sal,empno from emp where empno in (select mgr from emp)) t on e.ename not in (select ename from t);
ERROR 1146 (42S02): Table 'bjpowernode.t' doesn't exist
行不通耶。select ename from t的t不被认可,如果再写一个的话就太麻烦了。
那我试试找普通员工表,用ifnull把null除去试试看?但也不行。
select max(e.sal) from emp e join (select ename,sal,empno from emp where empno in (select mgr from emp)) t on e.ename !=t.ename;(不行,这个条件还是会得到所有人的名字)
换思路: 不用not in 用自连接试试:
distinct只能出现在所有字段的最前面。表示distinct后面的所有字段联合起来去除重复的记录。
1.找普通员工的表并按照sal降序排序:
select distinct a.ename,a.sal
from emp a
join emp b
on a.empno != b.mgr
order by a.sal desc;
(不对,因为这样实际上是得到了所有员工的名字)
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
还是要反向找领导的名字:
select distinct a.ename,a.sal
from emp a
join emp b
on a.empno = b.mgr
order by a.sal desc;
(这是对的)
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
+-------+---------+
6 rows in set (0.00 sec)
2.找出普通员工的最高薪资:
这步又回到了之前有问题的地方了,看答案,其实我想到了NULL的问题,但我想偏了,应该用is not null而不是ifnull,并且这部分也不需要像我那样用临时表
@@@@@@@@
比“普通员工的最高薪水”还要高的一定是领导!
没毛病!!!!
mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
员工编号没有在以上范围内的都是普通员工。(这就是领导表,我自己想的太复杂了。实际上MGR就是上级领导呀,只要用distinct选出来就好。。)
第一步:找出普通员工的最高薪水!
not in在使用的时候,后面小括号中记得排除NULL。
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+
第二步:找出高于1600的
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
10、取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5, 5;
+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
11、取得最后入职的 5 名员工
日期也可以降序,升序。
select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD | 1981-12-03 |
| JAMES | 1981-12-03 |
+--------+------------+
12、取得每个薪水等级有多少员工;
分组count
第一步:找出每个员工的薪水等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
±-------±--------±------+
| ename | sal | grade |
±-------±--------±------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±-------±--------±------+
第二步:继续按照grade分组统计数量(这里的count用法学一下)
select
s.grade ,count(*)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
s.grade;
±------±---------+
| grade | count(*) |
±------±---------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
±------±---------+
13、面试题;
有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出 2 门以上(含2 门)不及格学生姓名及平均成绩。
3,即学过 1 号课程又学过 2 号课所有学生的姓名。
请用标准sql语句写出答案,方言也行(请说明是使用什么方言)
14、列出所有员工及领导的姓名;
@@@@@@@@
我的思考
select a.ename 'YG ',b.ename 'LD' from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| YG | LD |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
@@@@@@@@
select
a.ename '员工', b.ename '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;
+--------+-------+
| 员工 | 领导 |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
@@@@@@@@
我的思考
select a.ename,a.empno,d.dname
from emp a
join emp b
on a.mgr=b.empno and a.hiredate<b.hiredate
join dept d
on a.deptno =d.deptno;
+-------+-------+------------+
| ename | empno | dname |
+-------+-------+------------+
| SMITH | 7369 | RESEARCH |
| ALLEN | 7499 | SALES |
| WARD | 7521 | SALES |
| JONES | 7566 | RESEARCH |
| BLAKE | 7698 | SALES |
| CLARK | 7782 | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.00 sec)
成功(答案是把and那个条件放在where里面了)
@@@@@@@@
emp a 员工表
emp b 领导表
a.mgr = b.empno and a.hiredate < b.hiredate
select
a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, d.dname
from
emp a
join
emp b
on
a.mgr = b.empno
join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate;
+-------+------------+-------+------------+------------+
| 员工 | hiredate | 领导 | hiredate | dname |
+-------+------------+-------+------------+------------+
| CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
+-------+------------+-------+------------+------------+
16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
@@@@@@@@
我的思考
同
select d.deptno,d.dname,e.ename 'YuanGong'
from dept d
left join emp e
on d.deptno=e.deptno;
+--------+------------+----------+
| deptno | dname | YuanGong |
+--------+------------+----------+
| 10 | ACCOUNTING | MILLER |
| 10 | ACCOUNTING | KING |
| 10 | ACCOUNTING | CLARK |
| 20 | RESEARCH | FORD |
| 20 | RESEARCH | ADAMS |
| 20 | RESEARCH | SCOTT |
| 20 | RESEARCH | JONES |
| 20 | RESEARCH | SMITH |
| 30 | SALES | JAMES |
| 30 | SALES | TURNER |
| 30 | SALES | BLAKE |
| 30 | SALES | MARTIN |
| 30 | SALES | WARD |
| 30 | SALES | ALLEN |
| 40 | OPERATIONS | NULL |
+--------+------------+----------+
15 rows in set (0.00 sec)
@@@@@@@@
select
e.*,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | ACCOUNTING |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | ACCOUNTING |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ACCOUNTING |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | RESEARCH |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | RESEARCH |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | RESEARCH |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | RESEARCH |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | RESEARCH |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | SALES |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | SALES |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | SALES |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | SALES |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | SALES |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | SALES |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
17、列出至少有 5 个员工的所有部门;
@@@@@@@@
我的思考
同
1,分组把每个部门的员工数找出来(不用管没有员工的部门,它肯定不是题目要找的)
select deptno,count(*)
from emp
group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
+--------+----------+
3 rows in set (0.01 sec)
2,把员工数>=5的找出来
select deptno,count(*)
from emp
group by deptno
having count(*)>=5;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 20 | 5 |
| 30 | 6 |
+--------+----------+
2 rows in set (0.00 sec)
成功
(注意这里count的用法,并且having 里面是可以有分组函数的)
@@@@@@@@
按照部门编号分组,计数,筛选出 >= 5
select
deptno
from
emp
group by
deptno
having
count(*) >= 5;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
+--------+
18、列出薪金比"SMITH" 多的所有员工信息
@@@@@@@@
我的思考
同
select *
from emp
where sal>(select sal from emp where ename='smith');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
@@@@@@@@
select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
@@@@@@@@
我的思考
select e.ename,e.job,d.dname,t.dnum
from emp e
join dept d
on e.deptno=d.deptno
join (select deptno,count(*) dnum from emp group by deptno) t
on e.deptno=t.deptno
where e.job='clerk'
+--------+-------+------------+------+
| ename | job | dname | dnum |
+--------+-------+------------+------+
| SMITH | CLERK | RESEARCH | 5 |
| ADAMS | CLERK | RESEARCH | 5 |
| JAMES | CLERK | SALES | 6 |
| MILLER | CLERK | ACCOUNTING | 3 |
+--------+-------+------------+------+
4 rows in set (0.00 sec)
成功,我的思想和答案一样,但是写的更好一点
@@@@@@@@
select ename,job from emp where job = 'CLERK';
+--------+-------+
| ename | job |
+--------+-------+
| SMITH | CLERK |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
+--------+-------+
select
e.ename,e.job,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK';
+--------+-------+------------+
| ename | job | dname |
+--------+-------+------------+
| MILLER | CLERK | ACCOUNTING |
| SMITH | CLERK | RESEARCH |
| ADAMS | CLERK | RESEARCH |
| JAMES | CLERK | SALES |
+--------+-------+------------+
select
e.ename,e.job,d.dname,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK';
+--------+-------+------------+--------+
| ename | job | dname | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING | 10 |
| SMITH | CLERK | RESEARCH | 20 |
| ADAMS | CLERK | RESEARCH | 20 |
| JAMES | CLERK | SALES | 30 |
+--------+-------+------------+--------+
//每个部门的人数?
select deptno, count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+-----------+
select
t1.*,t2.deptcount
from
(select
e.ename,e.job,d.dname,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK') t1
join
(select deptno, count(*) as deptcount from emp group by deptno) t2
on
t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename | job | dname | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING | 10 | 3 |
| SMITH | CLERK | RESEARCH | 20 | 5 |
| ADAMS | CLERK | RESEARCH | 20 | 5 |
| JAMES | CLERK | SALES | 30 | 6 |
+--------+-------+------------+--------+-----------+
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数;
@@@@@@@@
我的思考
1.按job分组找出每种job的最低薪资
select e.job,min(e.sal) minsal
from emp e
group by e.job;
+-----------+---------+
| job | minsal |
+-----------+---------+
| CLERK | 800.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
5 rows in set (0.00 sec)
2.筛选出最低薪金大于 1500 的工作
select e.job,min(e.sal) minsal
from emp e
group by e.job
having min(e.sal) >1500;
+-----------+------------+
| job | minsal |
+-----------+------------+
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+------------+
3 rows in set (0.00 sec)
3.加上工作的雇员人数
select e.job,min(e.sal) minsal,t.num
from emp e
join (select job,count(*) num from emp group by job) t
on t.job =e.job
group by e.job
having min(e.sal) >1500;
+-----------+---------+-----+
| job | minsal | num |
+-----------+---------+-----+
| MANAGER | 2450.00 | 3 |
| ANALYST | 3000.00 | 2 |
| PRESIDENT | 5000.00 | 1 |
+-----------+---------+-----+
3 rows in set (0.00 sec)
结果正确,但是我做复杂了,其实根本不需要用连接就能做,看答案,答案的count(*)要好好思考!!!
@@@@@@@@
按照工作岗位分组求最小值。
select job,count(*) from emp group by job having min(sal) > 1500;
(注意:这里的count(*)好好思考一下为何统计的是各种工作的雇员人数而不是筛过了min(sal) > 1500后的各个工作的雇员人数!!!!)
+-----------+----------+
| job | count(*) |
+-----------+----------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+----------+
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
±-------+
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select
e.ename '员工',d.dname,l.ename '领导',s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
left join
emp l
on
e.mgr = l.empno
join
salgrade s
on
e.sal between s.losal and s.hisal
where
e.sal > (select avg(sal) from emp);
+-------+------------+-------+-------+
| 员工 | dname | 领导 | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| KING | ACCOUNTING | NULL | 5 |
| FORD | RESEARCH | JONES | 4 |
+-------+------------+-------+-------+
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
select job from emp where ename = 'SCOTT';
+---------+
| job |
+---------+
| ANALYST |
+---------+
select
e.ename,e.job,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = (select job from emp where ename = 'SCOTT')
and
e.ename <> 'SCOTT';
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
select distinct sal from emp where deptno = 30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
select
ename,sal
from
emp
where
sal in(select distinct sal from emp where deptno = 30)
and
deptno <> 30;
Empty set (0.00 sec)
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
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);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
没有员工的部门,部门人数是0
select
d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;
+--------+--------+-------------+----------------+
| deptno | ecount | avgsal | avgservicetime |
+--------+--------+-------------+----------------+
| 10 | 3 | 2916.666667 | 38.0000 |
| 20 | 5 | 2175.000000 | 35.8000 |
| 30 | 6 | 1566.666667 | 38.3333 |
| 40 | 0 | 0.000000 | 0.0000 |
+--------+--------+-------------+----------------+
在mysql当中怎么计算两个日期的“年差”,差了多少年?
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())
间隔类型:
SECOND 秒,
MINUTE 分钟,
HOUR 小时,
DAY 天,
WEEK 星期
MONTH 月,
QUARTER 季度,
YEAR 年
27、 列出所有员工的姓名、部门名称和工资。
select
e.ename,d.dname,e.sal
from
emp e
join
dept d
on
e.deptno = d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| CLARK | ACCOUNTING | 2450.00 |
| KING | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH | RESEARCH | 800.00 |
| JONES | RESEARCH | 2975.00 |
| SCOTT | RESEARCH | 3000.00 |
| ADAMS | RESEARCH | 1100.00 |
| FORD | RESEARCH | 3000.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| TURNER | SALES | 1500.00 |
| JAMES | SALES | 950.00 |
+--------+------------+---------+
28、列出所有部门的详细信息和人数
select
d.deptno,d.dname,d.loc,count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+----------------+
29、列出各种工作的最低工资及从事此工作的雇员姓名
select
job,min(sal) as minsal
from
emp
group by
job;
+-----------+----------+
| job | minsal |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+----------+
emp e和以上t连接
select
e.ename,t.*
from
emp e
join
(select
job,min(sal) as minsal
from
emp
group by
job) t
on
e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
30、列出各个部门的 MANAGER( 领导) 的最低薪金
select
deptno, min(sal)
from
emp
where
job = 'MANAGER'
group by
deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+----------+
31、列出所有员工的 年工资, 按 年薪从低到高排序
select
ename,(sal + ifnull(comm,0)) * 12 as yearsal
from
emp
order by
yearsal asc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD | 21000.00 |
| ALLEN | 22800.00 |
| CLARK | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| FORD | 36000.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
+--------+----------+
32、求出员工领导的薪水超过3000的员工名称与领导
select
a.ename '员工',b.ename '领导'
from
emp a
join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;
+-------+------+
| 员工 | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+
33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
select
d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
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;
+--------+------------+---------+----------------+----------+
| deptno | dname | loc | count(e.ename) | sumsal |
+--------+------------+---------+----------------+----------+
| 20 | RESEARCH | DALLAS | 5 | 10875.00 |
| 30 | SALES | CHICAGO | 6 | 9400.00 |
| 40 | OPERATIONS | BOSTON | 0 | 0.00 |
+--------+------------+---------+----------------+----------+
34、给任职日期超过 30 年的员工加薪 10%.
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;