多表查询
1.列出每个部门的平均收入及部门名称;第一种方法：
select * from dept,emp where dept.dept1=emp.dept2; 临时表t
select dept_name,avg(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t group by t.dept_name;
第二种：
select dept2,avg(incoming) from emp group by dept2;
select dept_name,t.a from (select dept2,avg(incoming)a from emp group by dept2)t,dept where t.dept2=dept.dept1; #注意：查询的字段有引用后面临时表里面的聚合函数字段，需要取别名
2.财务部门的收入总和；
第一种：临时表
select sum(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where t.dept_name= '财务';
第二种：嵌套
select dept1 from dept where dept_name='财务'
select sum(incoming) from emp group by dept2 having dept2=(select dept1 from dept where dept_name='财务');
第三种：
select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务');
第四种：基本连接扩展用法
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';
3.It技术部入职员工的员工号;
第一种：嵌套
select sid from emp where dept2=(select dept1 from dept where dept_name='IT技术');
第二种：基本连接
select sid from dept,emp where dept.dept1=emp.dept2 and dept_name='IT技术';
第三种：临时表
select sid from (select * from dept inner join emp on dept.dept1=emp.dept2)t where t.dept_name='IT技术';
4.财务部门收入超过2000元的员工姓名;
第一种：基本连接
select name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming>2000;
第二种：嵌套
select name from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name='财务');
第三种：临时表
select name from (select * from dept inner join emp on dept.dept1=emp.dept2)t where t.dept_name='财务' and t.incoming>2000;
5.找出销售部收入最低的员工的入职时间；
#第一种：
先求销售部最低的收入：
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='销售');
再求入职时间：
①select worktime_start from emp where incoming in(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='销售')) and dept2=(select dept1 from dept where dept_name='销售');
②select worktime_start from emp,dept where incoming in(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='销售')) and dept.dept1=emp.dept2 and dept_name = '销售';
第二种：
select worktime_start from emp,dept where incoming in (select min(incoming) from emp group by dept2) and dept_name='销售' and dept.dept1=emp.dept2;
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称;
先求平均年龄：
select avg(age) from emp;
select name,sid,dept_name from dept,emp where dept.dept1=emp.dept2 and age<(select avg(age) from emp);
7.列出每个部门收入总和高于9000的部门名称;
第一种：先求每个部门的总收入和部门号
select dept2,sum(incoming)s from emp group by dept2 having s>9000;
select dept.dept_name from (select dept2,sum(incoming)s from emp group by dept2 having s>9000)t,dept where dept.dept1=t.dept2;
第二种：临时表
select * from dept,emp where dept.dept1=emp.dept2;
select dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t group by dept1 having sum(incoming)>9000;
第三种：基本连接
select dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000;
8.查出财务部门工资少于3800元的员工姓名;
第一种：
select name from emp where incoming<3800 and dept2=(select dept1 from dept where dept_name='财务');
第二种：
select name from dept,emp where dept.dept1=emp.dept2 and incoming<3800 and dept_name='财务';
9.求财务部门最低工资的员工姓名;
第一种：
先求财务部门最低工资：
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务');
①select name from emp where incoming=(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务')) and dept2=(select dept1 from dept where dept_name='财务');
②select name from emp,dept where incoming=(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务')) and dept.dept1=emp.dept2 and dept_name='财务';
第二种：
select min(incoming) from emp group by dept2;#先求每个部门的最低收入
select name from emp where incoming in(select min(incoming) from emp group by dept2) and dept2=(select dept1 from dept where dept_name='财务');
10.找出销售部门中年纪最大的员工的姓名;
第一种：
select max(age) from emp group by dept2;
select name from emp where age in(select max(age) from emp group by dept2) and dept2=(select dept1 from dept where dept_name='销售');
第二种：
select name from emp,dept where dept.dept1=emp.dept2 and dept_name='销售' and age=(select max(age) from emp where dept2=(select dept1 from dept where dept_name='销售'));
11.求收入最低的员工姓名及所属部门名称;
select min(incoming) from emp;
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from emp);
12.求李四的收入及部门名称;
select incoming,dept_name from dept,emp where dept.dept1=emp.dept2 and name='李四';
13.求员工收入小于4000元的员工部门编号及其部门名称;
select dept_name,sid from dept,emp where dept.dept1=emp.dept2 and incoming<4000;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序;
第一种：
select dept2,max(incoming)m from emp group by dept2; 临时表a
select * from dept,emp where dept.dept1=emp.dept2; 临时表b
select b.name,b.dept_name,b.incoming from (select dept2,max(incoming)m from emp group by dept2)a,(select * from dept,emp where dept.dept1=emp.dept2)b where a.dept2=b.dept1 and a.m=b.incoming order by incoming desc;
第二种：
select name,dept_name,incoming from (select * from emp,dept where emp.dept2=dept.dept1)t where incoming in (select max(incoming) from emp group by dept2) order by incoming desc
第三种：
select name,dept_name,incoming from emp,dept where dept.dept1=emp.dept2 and incoming in (select max(incoming) from emp group by dept2) order by incoming desc;
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select name,sid,incoming from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' order by incoming desc limit 2;
select name,sid,incoming from emp where dept2=(select dept1 from dept where dept_name='财务') order by incoming desc limit 2;
16.查询财务部低于平均收入的员工号与员工姓名
select sid,name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming<(select avg(incoming) from emp);
17.列出部门员工数大于1个的部门名称；
第一种：
select dept2,count(name)c from emp group by dept2 having c>1;
select dept_name from dept,(select dept2,count(name)c from emp group by dept2 having c>1)t where dept.dept1=t.dept2;
第二种：
select dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name having count(name)>1;
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号;
select age,dept2 from emp where incoming>3000 and incoming<=7500;
19.求入职于20世纪70年代的员工所属部门名称;
select dept_name from dept,emp where dept.dept1=emp.dept2 and worktime_start>='1970-01-01' and worktime_start<'1980-01-01';
select dept_name from dept,emp where dept.dept1=emp.dept2 and worktime_start like '197%';
20.查找张三所在的部门名称;
第一种：嵌套
select dept_name from dept where dept1=(select dept2 from emp where name='张三');
第二种：基本连接
select dept_name from dept,emp where dept.dept1=emp.dept2 and name='张三';
21.列出每一个部门中年纪最大的员工姓名，部门名称；
每个部门最大的年龄和部门号：
select max(age) from emp group by dept2;
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and age in (select max(age) from emp group by dept2);
select name,dept_name from (select dept2,dept_name,max(age)m from dept,emp where dept.dept1=emp.dept2 group by dept2)t,emp where emp.dept2=t.dept2 and age=t.m;
22.列出每一个部门的员工总收入及部门名称;
select sum(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name;
23.列出部门员工收入大于7000的员工号，部门名称;
select sid,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>7000;
24.找出哪个部门还没有员工入职；
第一种：
select * from dept left join emp on dept.dept1=emp.dept2;
select dept_name from (select * from dept left join emp on dept.dept1=emp.dept2)t where t.name is null;
第二种：
select dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name having count(name)<1;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表；
select * from emp order by dept2 desc,worktime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
select name,sid from emp where incoming=(select max(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务')) and dept2=(select dept1 from dept where dept_name='财务');
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
select dept_name,name from dept,emp where dept.dept1=emp.dept2 and age in(select max(age) from emp ) and incoming between 7500 and 8500 order by incoming desc limit 1;
第一种：
select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500;
select max(age) from (select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500)t;
select name,dept_name from (select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500)t where t.age=(select max(age) from (select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500)t);
第二种：
select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500;
select name,dept_name from (select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500)t where t.age=(select max(age) from (select * from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500)t);
第三种：
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500 and age in(select max(age) from emp group by dept2);
页:
[1]