子查询（in not ）转化为连接查询的方法（很神奇的，BI可以借鉴）

引用

   我们进行数据查询的时候极少有可能就在一张表里就能得到想要的数据，不可避免得会用到子查询或者连接查询，很多时候我们很轻松自然得会想到子查询的方法，但是子查询往往效率比较低，而转换成连接查询是一种很好的优化方式。 

    子查询转换成连接查询又可以分为两种情况，一种是不带聚合的子查询转换，另一种就是带有聚合函数的转换 

一、不带聚合函数的子查询转换： 

以下是一组测试数据： 

Java代码  

1. Sql代码     
2. use mytest;      
3. drop table  if exists jobs;      
4. CREATE TABLE jobs(      
5.     employee varchar(30),      
6.     title varchar(30)      
7. );      
8. drop table if exists ranks;      
9. CREATE TABLE ranks(      
10.     title varchar(30),      
11.     rank varchar(30)      
12. );      
13. drop table if exists salary;      
14. CREATE TABLE salary(      
15.     rank varchar(30),      
16.     payment int(11)      
17. );      
18.      
19. insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');      
20. insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');      
21. insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);     
22.   
23. use mytest;   
24. drop table  if exists jobs;   
25. CREATE TABLE jobs(   
26.     employee varchar(30),   
27.     title varchar(30)   
28. );   
29. drop table if exists ranks;   
30. CREATE TABLE ranks(   
31.     title varchar(30),   
32.     rank varchar(30)   
33. );   
34. drop table if exists salary;   
35. CREATE TABLE salary(   
36.     rank varchar(30),   
37.     payment int(11)   
38. );   
39.   
40. insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');   
41. insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');   
42. insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);  

[java] view plaincopy

1. Sql代码    
2. use mytest;     
3. drop table  if exists jobs;     
4. CREATE TABLE jobs(     
5.     employee varchar(30),     
6.     title varchar(30)     
7. );     
8. drop table if exists ranks;     
9. CREATE TABLE ranks(     
10.     title varchar(30),     
11.     rank varchar(30)     
12. );     
13. drop table if exists salary;     
14. CREATE TABLE salary(     
15.     rank varchar(30),     
16.     payment int(11)     
17. );     
18.     
19. insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');     
20. insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');     
21. insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);    
22.   
23. use mytest;  
24. drop table  if exists jobs;  
25. CREATE TABLE jobs(  
26.     employee varchar(30),  
27.     title varchar(30)  
28. );  
29. drop table if exists ranks;  
30. CREATE TABLE ranks(  
31.     title varchar(30),  
32.     rank varchar(30)  
33. );  
34. drop table if exists salary;  
35. CREATE TABLE salary(  
36.     rank varchar(30),  
37.     payment int(11)  
38. );  
39.   
40. insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');  
41. insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');  
42. insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);  

   建立了三个表，分别是jobs员工工作表，记录了员工的工作，第二表ranks是岗位等级表，记录每一个工作岗位的等级，第三个表slary自然就是HR为每一个等级的定的薪资标准了。 

    现在要知道张三的工资是多少，就需要使用三张表才能得到数据， 
使用子查询的方法如下： 

Java代码  

1. Sql代码     
2. select payment from salary       
3.     where rank=(      
4.         SELECT rank from ranks       
5.             where title=(      
6.                 SELECT title from jobs       
7.                     where employee='张三')      
8. );     
9.   
10. select payment from salary    
11.     where rank=(   
12.         SELECT rank from ranks    
13.             where title=(   
14.                 SELECT title from jobs    
15.                     where employee='张三')   
16. );  

[java] view plaincopy

1. Sql代码    
2. select payment from salary      
3.     where rank=(     
4.         SELECT rank from ranks      
5.             where title=(     
6.                 SELECT title from jobs      
7.                     where employee='张三')     
8. );    
9.   
10. select payment from salary   
11.     where rank=(  
12.         SELECT rank from ranks   
13.             where title=(  
14.                 SELECT title from jobs   
15.                     where employee='张三')  
16. );  

转换为连接查询的步骤大致有如下几点： 
1、使用表名或者表别名标记所有的列，如显jobs.employee 或者j.employee； 
2、将几个子查询的From子名中使用的相同的表用同一个名字或同一别名； 
3、将几个Form子句放在一起； 
4、将Select及查询的列删除； 
5、将第一个之后的Where替换成AND 

最后得到如下结果： 

Java代码  

1. Sql代码     
2. select payment from salary s,ranks r,jobs j       
3.     where j.employee='张三'       
4.         and j.title = r.title       
5.         and s.rank = r.rank;     
6.   
7. select payment from salary s,ranks r,jobs j    
8.     where j.employee='张三'    
9.         and j.title = r.title    
10.         and s.rank = r.rank;  

[java] view plaincopy

1. Sql代码    
2. select payment from salary s,ranks r,jobs j      
3.     where j.employee='张三'      
4.         and j.title = r.title      
5.         and s.rank = r.rank;    
6.   
7. select payment from salary s,ranks r,jobs j   
8.     where j.employee='张三'   
9.         and j.title = r.title   
10.         and s.rank = r.rank;  

对于需要排除某些条件的查询，如查询岗位等级表中在薪资表中没有工资级别的等级： 

Java代码  

1. Java代码     
2. select salary.rank       
3.     from salary       
4.         where rank       
5.             not in(select rank from ranks);     
6.   
7. select salary.rank    
8.     from salary    
9.         where rank    
10.             not in(select rank from ranks);  

[java] view plaincopy

1. Java代码    
2. select salary.rank      
3.     from salary      
4.         where rank      
5.             not in(select rank from ranks);    
6.   
7. select salary.rank   
8.     from salary   
9.         where rank   
10.             not in(select rank from ranks);  

使用not in、exists、not exists不失为一种好方法，但同样可以转换成连接查询。如以上的查询可以转换为： 

Java代码  

1. Sql代码     
2. select salary.rank       
3.     from salary left join ranks       
4.         on salary.rank=ranks.rank       
5.             where ranks.rank is null;     
6.   
7. select salary.rank    
8.     from salary left join ranks    
9.         on salary.rank=ranks.rank    
10.             where ranks.rank is null;  

[java] view plaincopy

1. Sql代码    
2. select salary.rank      
3.     from salary left join ranks      
4.         on salary.rank=ranks.rank      
5.             where ranks.rank is null;    
6.   
7. select salary.rank   
8.     from salary left join ranks   
9.         on salary.rank=ranks.rank   
10.             where ranks.rank is null;  

二、带聚合函数的子查询向连接查询转换 

如下测试数据，有一个订单表，记录了销售人员每天的销售记录，测试数据如下： 

Java代码  

1. Sql代码     
2.      
3. DROP TABLE if exists orders;      
4. create table orders(      
5.     customer varchar(30),      
6.     whn date,      
7.     totalitems int(11)      
8. );      
9. insert into orders values('jj','2010-10-10',5),      
10.             ('jj','2010-10-11',3),      
11.             ('jj','2010-10-12',1),      
12.             ('aa','2010-10-10',5),      
13.             ('bb','2010-10-10',8),      
14.             ('cc','2010-10-10',10);     
15.   
16.   
17. DROP TABLE if exists orders;   
18. create table orders(   
19.     customer varchar(30),   
20.     whn date,   
21.     totalitems int(11)   
22. );   
23. insert into orders values('jj','2010-10-10',5),   
24.             ('jj','2010-10-11',3),   
25.             ('jj','2010-10-12',1),   
26.             ('aa','2010-10-10',5),   
27.             ('bb','2010-10-10',8),   
28.             ('cc','2010-10-10',10);  

[java] view plaincopy

1. Sql代码    
2.     
3. DROP TABLE if exists orders;     
4. create table orders(     
5.     customer varchar(30),     
6.     whn date,     
7.     totalitems int(11)     
8. );     
9. insert into orders values('jj','2010-10-10',5),     
10.             ('jj','2010-10-11',3),     
11.             ('jj','2010-10-12',1),     
12.             ('aa','2010-10-10',5),     
13.             ('bb','2010-10-10',8),     
14.             ('cc','2010-10-10',10);    
15.   
16.   
17. DROP TABLE if exists orders;  
18. create table orders(  
19.     customer varchar(30),  
20.     whn date,  
21.     totalitems int(11)  
22. );  
23. insert into orders values('jj','2010-10-10',5),  
24.             ('jj','2010-10-11',3),  
25.             ('jj','2010-10-12',1),  
26.             ('aa','2010-10-10',5),  
27.             ('bb','2010-10-10',8),  
28.             ('cc','2010-10-10',10);  

需要查询每一个销售员最高销售额的日期及销售额时，必然用的聚合函数MAX，以下是最容易想到的查询方式： 

Java代码  

1. Sql代码     
2. select customer,whn,totalitems       
3.     from orders o1 where o1.totalitems=(      
4.         SELECT max(totalitems)       
5.             from orders o2       
6.                 where o1.customer = o2.customer      
7. );     
8.   
9. select customer,whn,totalitems    
10.     from orders o1 where o1.totalitems=(   
11.         SELECT max(totalitems)    
12.             from orders o2    
13.                 where o1.customer = o2.customer   
14. );  

[java] view plaincopy

1. Sql代码    
2. select customer,whn,totalitems      
3.     from orders o1 where o1.totalitems=(     
4.         SELECT max(totalitems)      
5.             from orders o2      
6.                 where o1.customer = o2.customer     
7. );    
8.   
9. select customer,whn,totalitems   
10.     from orders o1 where o1.totalitems=(  
11.         SELECT max(totalitems)   
12.             from orders o2   
13.                 where o1.customer = o2.customer  
14. );  

此时需要对每一行订单都要进行子查询，因此代码运行速度会很慢，并且老版本的MySQL还不支持子查询，只有一个表，要改成连接查询自然就是自连接了，这里我们需要使用Having子句， 

Java代码  

1. Sql代码     
2. select o1.* from orders o1 join orders o2       
3.     on(o1.customer=o2.customer)       
4.         group by o1.customer       
5.             having o1.totalitems=max(o2.totalitems      
6. );     
7.   
8. select o1.* from orders o1 join orders o2    
9.     on(o1.customer=o2.customer)    
10.         group by o1.customer    
11.             having o1.totalitems=max(o2.totalitems   
12. );  

[java] view plaincopy

1. Sql代码    
2. select o1.* from orders o1 join orders o2      
3.     on(o1.customer=o2.customer)      
4.         group by o1.customer      
5.             having o1.totalitems=max(o2.totalitems     
6. );    
7.   
8. select o1.* from orders o1 join orders o2   
9.     on(o1.customer=o2.customer)   
10.         group by o1.customer   
11.             having o1.totalitems=max(o2.totalitems  
12. );  

相信这些我们大学的时候都已经学过，但是没有真正用起来的时候总是那么容易忘记，没有实际操作和体验是感觉不到它的需要，自然也不长记性了，而写下来又是另一种记住的方式。