目录

关联测试文章：

（新建表结构）新增员工表emp和部门表dept

1.找出销售部门中年纪最大的员工的姓名

2.求财务部门最低工资的员工姓名

3.列出每个部门收入总和高于9000的部门名称

4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

5.找出销售部门收入最低的员工入职时间

6.财务部门收入超过2000元的员工姓名

7.列出每个部门的平均收入及部门名称

8.IT技术部入职员工的员工号

9.财务部门的收入总和；

10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表

11.找出哪个部门还没有员工入职；

12.列出部门员工收入大于7000的部门编号，部门名称；

13.列出每一个部门的员工总收入及部门名称；

14.列出每一个部门中年纪最大的员工姓名，部门名称；

15.求李四的收入及部门名称

16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序

17.列出部门员工数大于1个的部门名称

19.查找张三所在的部门名称

 五、结束语--------------------------------------------------------------------------

点击下面关联文章链接，即可跳转关联文章查看界面：

 《第五部分：Mysql在Linux环境中使用、以及了解BUG》

 create table dept (dept1 int ,dept_name varchar(11));

 create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int);

 insert into dept values

(101,'财务'),

(102,'销售'),

(103,'IT技术'),

(104,'行政');

 insert into emp values

(1789,'张三',35,'1980/1/1',4000,101),

(1674,'李四',32,'1983/4/1',3500,101),

(1776,'王五',24,'1990/7/1',2000,101),

(1568,'赵六',57,'1970/10/11',7500,102),

(1564,'荣七',64,'1963/10/11',8500,102),

(1879,'牛八',55,'1971/10/20',7300,103);

【方法一】

select * from emp

where

age=(select MAX(age) AS age from emp where dept2=(select dept1 from dept where dept_name='销售'))

and

dept2 =(select dept1 from dept where dept_name='销售')

【方法二】

select * from emp  

RIGHT JOIN (select MAX(age) AS age,dept2 from emp where dept2=(select dept1 from dept where dept_name='销售')) new1

on emp.dept2=new1.dept2 and emp.age=new1.age

select * from emp

where

incoming =(select MIN(incoming) AS incoming  from emp where dept2=(select dept1 from dept where dept_name='财务'))

and

dept2 =(select dept1 from dept where dept_name='财务')

SELECT dept.dept1,dept.dept_name from dept RIGHT JOIN (

SELECT aa.dept2 as dept2  from (select * FROM emp,dept where emp.dept2=dept.dept1) aa

GROUP BY dept2

HAVING SUM(incoming)>9000) deptNew

ON dept.dept1=deptNew.dept2

（1）方法一

select * from (select * from emp where incoming BETWEEN 7500 and 8500) new1

right JOIN

(select MAX(new2.age) as age from (select * from emp where incoming BETWEEN 7500 and 8500) new2) new3

on new1.age=new3.age

（2）方法二

select  * from dept  INNER JOIN

(select  * from emp where incoming between 7500 and 8500)new1

on dept.dept1 = new1.dept2 AND

new1.age=(select  max(age) as c from emp where incoming between 7500 and 8500);

select * from emp  

RIGHT JOIN (select min(incoming) AS incoming,dept2 from emp where dept2=(select dept1 from dept where dept_name='销售')) new1

On emp.dept2=new1.dept2  and  emp.incoming=new1.incoming

select * from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name='财务' )

select * from dept RIGHT JOIN

(select AVG(incoming) as avgIncoming,dept2 from emp GROUP BY dept2) new1

on dept.dept1=new1.dept2

select * from emp where dept2=(select dept1 from dept where dept_name='IT技术' )

select SUM(incoming) as 收入总和 from emp where dept2=(select dept1 from dept where dept_name='财务' )

（1）方法一：不包含不存在员工的部门

SELECT * from emp order by dept2 desc,worktime_start asc;

（2）方法二：包含不存在员工的部门

SELECT * from emp RIGHT JOIN dept on dept.dept1=emp.dept2 order by dept.dept1 desc,worktime_start asc;

select * from dept where dept1 not in(SELECT dept2 from emp)

（1）方法一：使用链接方法查找并去重

select * from dept where dept1 in (

select DISTINCT(dept.dept1) as dept1 from dept RIGHT JOIN  (

select * from emp where incoming>7000) new1

 on dept.dept1=new1.dept2);

（2）方法二：使用临时表查询

select * from dept where dept1 in (select dept2 from emp where incoming>7000);

select * from dept RIGHT JOIN (select dept2, SUM(incoming) from emp GROUP BY  dept2) new1

on dept.dept1=new1.dept2

select * from dept inner JOIN

(select emp.name,emp.age,emp.dept2 from emp

inner JOIN (select dept2, max(age)as age from emp GROUP BY  dept2) new1

on emp.dept2=new1.dept2 AND emp.age=new1.age)new2

ON dept.dept1=new2.dept2

select * from emp INNER JOIN dept ON emp.dept2=dept.dept1 where emp.name='李四'

（1）方法一：注意需要进行多次联查（目的：解决同一个部门存在有多个最大值问题）

select * from dept INNER JOIN

(SELECT emp.name,emp.incoming,emp.dept2 from emp RIGHT JOIN

(select MAX(incoming) as incoming ,dept2 from emp group by dept2)new1  

 on emp.dept2=new1.dept2 and emp.incoming=new1.incoming) new2

On dept.dept1=new2.dept2

select * from dept where dept1 in (select  dept2 from emp group by dept2 having COUNT(dept2) >1)

（1）方法一：

SELECT * from dept INNER JOIN

(select * from emp where worktime_start BETWEEN '1970-01-01' and '1979-12-31') new1

on dept.dept1=new1.dept2;

（2）方法二

SELECT * from dept INNER JOIN

(select * from emp where worktime_start >='1970-01-01' and  worktime_start=1970-01-1" and worktime start