《第五部分-练习题2：MySQL两表联合查询》

 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<='1979-12-31') new1

on dept.dept1=new1.dept2;

#第一种方法:在dept表中找出部门编号在上面的数据的部门名称

select dept_name from dept where deptl in (select dent2 from emp where worktime_start BETWEEN"1970-01-01" and "1979-12-31"); 

#第二种方法

 select dept_name from dept LEEr JorN emp on dept.dept1=emp.dept2 where worktime_start BETWEEX “"1970-01-01" and "1979-12-31";

#第三种方法

select dept name from dept 1ET JON emp on dept.dept1=emp.dept2 where worktime_start>=1970-01-1" and worktime start<=1979-12-31”

#第四种方法

 select dept name from dept LEFT JOTN emp on dept.deptl=emp.dept2 where worktime_start like "197%";

（1）方法一：嵌套子查询

select * from dept where dept1 in (select dept2 from emp where name='张三')

（2）方法二：左查询

select * from  (select * from emp where name='张三') new1 LEFT JOIN (select * from dept) new2  on new2.dept1=new1.dept2;

（3）方法三：右查询

select * from  (select * from dept) new2 RIGHT  JOIN (select * from emp where name='张三') new1  on new2.dept1=new1.dept2;

（4）方法四：内查询

select * from (select * from dept)  new2 iNNER   JOIN (select * from emp where name='张三') new1   on new2.dept1=new1.dept2;

（5）方法五：基本查询

select * from dept,emp where dept.dept1=emp.dept2 and  emp.name='张三'

（6）方法六：临时表

select * from (select * from dept LEFT JOIN emp on dept.dept1=emp.dept2) new1 where new1.name="张三"