两组表单看懂MySQL的多表查询

 第一组表单信息

1、查询每个部门的所属员工

mysql> SELECT name,GROUP_CONCAT(ename) persons
    -> FROM dept3 d
    -> LEFT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY d.deptno

    -> UNION 

    -> SELECT name,GROUP_CONCAT(ename) persons
    -> FROM dept3 d
    -> RIGHT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY e.dept_id
    -> ;

2、查询研发部门的所属员工

mysql> select name,group_concat(ename)
    -> from dept3 d,emp3 e
    -> where name = '研发部' and d.deptno=e.dept_id
    -> group by name;

3、查询研发部和销售部的所属员工

mysql> select name,group_concat(ename)
    -> from dept3 d,emp3 e
    -> where (name='研发部' or name='销售部')
    -> and d.deptno=e.dept_id
    -> group by name;

 

4、查询每个部门的员工数,并升序排序

mysql> SELECT name,COUNT(ename) persons
    -> FROM dept3 d
    -> LEFT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY d.deptno
    -> 
    -> UNION
    -> 
    -> SELECT name,COUNT(ename) persons
    -> FROM dept3 d
    -> RIGHT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY e.dept_id
    -> ORDER BY persons
    -> ;

5、查询人数大于等于3的部门，并按照人数降序排序

mysql> SELECT name,COUNT(ename) persons
    -> FROM dept3 d
    -> LEFT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY d.deptno
    -> HAVING persons > 3
    ->    
    -> UNION
    ->      
    -> SELECT name,COUNT(ename) persons
    -> FROM dept3 d
    -> RIGHT JOIN emp3 e
    -> ON d.deptno = e.dept_id
    -> GROUP BY e.dept_id
    -> HAVING persons > 3
    -> ORDER BY persons
    -> ;

---

第二组表单信息

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

mysql> select name from dept,emp
    -> where dept1=dept2
    -> and age=(select max(age) from emp where dept_name='销售')
    -> ;

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

mysql> select name from dept,emp
    -> where dept1=dept2
    -> and incoming=(select min(incoming) from emp where dept_name='财务')
    -> ;

 

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

mysql> SELECT dept.dept_name,SUM(emp.incoming) AS total
    -> FROM dept
    -> LEFT JOIN emp 
    -> ON dept.dept1 = emp.dept2
    -> GROUP BY dept.dept_name
    -> HAVING total > 9000
    -> ;

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

mysql> SELECT d.dept_name,e.name
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Where incoming Between 7500 And 8500
    -> Order By e.age Desc
    -> Limit 1
    -> ;

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

mysql> SELECT e.worktime_start
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Where d.dept_name='销售'
    -> Order By e.incoming
    -> Limit 1
    -> ;

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

mysql> SELECT e.name
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Where d.dept_name='财务'
    -> And incoming > 2000
    -> ;

  

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

mysql> SELECT d.dept_name,avg(e.incoming)
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Group By d.dept_name
    -> ;

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

mysql> SELECT sid
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> where d.dept_name='IT技术'
    -> ;

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

mysql> SELECT sum(e.incoming) total
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Group By d.dept_name
    -> Having d.dept_name='财务'
    -> ;

  

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

mysql> SELECT *
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Order By dept1 Desc,worktime_start Asc
    -> ;

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

mysql> SELECT dept_name
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Where e.name Is Null 
    -> ;

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

在MySQL的ONLY_FULL_GROUP_BY模式下，所有非聚合的SELECT列都必须包含在GROUP BY子句中，并且SELECT列表中的列必须对GROUP BY子句中的列有功能上的依赖关系。

mysql> SELECT dept1 As dept_num,d.dept_name,Max(e.incoming)
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Group By d.dept1,d.dept_name
    -> Having Max(incoming) > 7000
    -> ;

 

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

mysql> SELECT dept_name,Sum(e.incoming)
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Group By d.dept1,d.dept_name
    -> ;

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

子查询
mysql> SELECT d.dept_name, e.name, e.age
    -> FROM dept d
    -> LEFT JOIN emp e ON d.dept1 = e.dept2
    ->      JOIN (
    ->            SELECT dept2, MAX(age) AS max_age
    ->            FROM emp
    ->            GROUP BY dept2
    ->           ) AS m
    ->       ON e.dept2 = m.dept2 AND e.age = m.max_age
    -> ;

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

mysql> SELECT dept_name,name,incoming
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Having name='李四'
    -> ;

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

mysql> SELECT dept_name,name,incoming
    -> FROM dept d
    -> LEFT JOIN emp e 
    -> ON d.dept1 = e.dept2
    -> Where e.incoming Is Null 
    -> Or (e.incoming=(Select Max(incoming) 
    ->                 From emp
    ->                 Where dept2=e.dept2 
    ->                )
    ->    )
    -> ;

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

mysql> SELECT dept_name
    -> FROM dept
    -> WHERE dept1 IN (
    ->     SELECT dept2
    ->     FROM emp
    ->     GROUP BY dept2
    ->     HAVING COUNT(*) > 1
    -> );

  

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

mysql> SELECT dept_name
    -> FROM dept
    -> WHERE dept1 IN (
    ->     SELECT dept2
    ->     FROM emp
    ->     Where name='张三'
    -> );