mysql 多表查询、子查询、自关联查询

一、多表查询

包含连接查询合并方案、联合查询合并方案

1、连接查询合并方案（水平连接） JOIN…on…

内连接

SELECT * FROM dispatch_list JOIN man 
ON man.responsibility_man=dispatch_list.responsibility_man;

外连接

包含左外连接、右外连接；
没有值的栏位用null显示 （例子：没有派工单的工号也显示出来）
连接查询合并方案，使用外连接(确保“需要帮助表”的所有数据行都要进入到合法临时表)过滤合法数据；

左外连接： FROM 需要帮助表  LEFT JOIN 不需要帮助表  ON 条件
SELECT * FROM man LEFT JOIN dispatch_list 
ON man.responsibility_man=dispatch_list.responsibility_man;

右外连接： FROM 不需要帮助表  RIGHT JOIN 需要帮助表  ON 条件
SELECT * FROM dispatch_list RIGHT JOIN man 
ON man.responsibility_man=dispatch_list.responsibility_man;

2、联合查询合并方案（垂直合并）

两张临时表结构必须一致，生成临时表字段只能来自第一个临时表；
UNION ALL不会去重，UNION会去重；联合查询合并方案只用于行转列查询；

SELECT responsibility_man, responsibility_name FROM dispatch_list 
UNION 
SELECT responsibility_man, man_name FROM man;

二、子查询

子查询：包含独立子查询、依赖子查询（一般不使用）

1、独立子查询

独立子查询规则：（不需要帮助就可以执行）
1、独立子查询返回的结果相当于一个常量；
2、独立子查询在被帮助命令前执行一次；
3、在被帮助命令执行期间，独立子查询不会执行；

SELECT doc_no, responsibility_man, use_work_hour, (SELECT AVG(use_work_hour) FROM dispatch_list) AS avg_hour 
FROM dispatch_list 
WHERE use_work_hour>(SELECT AVG(use_work_hour) FROM dispatch_list);

2、依赖子查询

依赖子查询：必须得到被帮助命令提供服务才能正常执行；
依赖子查询规则：
1、依赖子查询根据接收参数不同，返回结果不同；
2、依赖子查询在被帮助的命令执行时被调用，会无限增加加载表文件的次数，严重影响效率；
3、在实际开发过程中禁止使用依赖子查询；可以使用连接查询、自关联查询替代；

例子：获取所用工时大于该人员平均工时的派工单；
SELECT t2.doc_no,t2.responsibility_man,t2.use_work_hour, (SELECT AVG(t1.use_work_hour) AS avg_hour FROM dispatch_list t1 WHERE t1.responsibility_man=t2.responsibility_man) AS avg_hour  
FROM dispatch_list t2 
WHERE t2.use_work_hour>(SELECT AVG(t1.use_work_hour) FROM dispatch_list t1 WHERE t1.responsibility_man=t2.responsibility_man) 
ORDER BY t2.responsibility_man, avg_hour, use_work_hour DESC, doc_no;

用连接代替依赖子查询，效率更快；如：

SELECT t1.doc_no, t1.responsibility_man, t1.use_work_hour, t2.avg_hour 
FROM dispatch_list t1 
JOIN (SELECT responsibility_man, AVG(use_work_hour) AS avg_hour 
	  FROM dispatch_list 
	  GROUP BY responsibility_man ) t2 
ON t1.responsibility_man=t2.responsibility_man 
WHERE t1.use_work_hour>t2.avg_hour;

三、自关联查询

自关联查询：
1、是连接查询的一种特殊表现形式；
2、将同一张表分别加载到内存中两次，再进行合并；
3、从一张临时表读取数据，另一张临时表提供参考数据；
4、不是每张表都适合使用自关联查询；有隶属关系才适合；

SELECT t1.responsibility_man, t1.review_man, t2.responsibility_man, t2.doc_no, t2.assign_content 
FROM dispatch_list t1 JOIN dispatch_list t2 
	ON t1.review_man=t2.responsibility_man 
GROUP BY t2.doc_no, t2.responsibility_man 
ORDER BY t2.responsibility_man;