子查询

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

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

以下是一组测试数据： 

use mytest;
drop table  if exists jobs;
CREATE TABLE jobs(
    employee varchar(30),
    title varchar(30)
);
drop table if exists ranks;
CREATE TABLE ranks(
    title varchar(30),
    rank varchar(30)
);
drop table if exists salary;
CREATE TABLE salary(
    rank varchar(30),
    payment int(11)
);

insert into jobs values('张三','经理'),('李四','总经理'),('王五','总经理助理');
insert into ranks values('经理','三'),('总经理','一'),('总经理助理','二');
insert into salary values('一',20000),('二',8000),('三',7000),('四',7000);

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

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

select payment from salary
    where rank=(
        SELECT rank from ranks
            where title=(
                SELECT title from jobs
                    where employee='张三')
);

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

最后得到如下结果： 

select payment from salary s,ranks r,jobs j
    where j.employee='张三'
        and j.title = r.title
        and s.rank = r.rank;

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

select salary.rank
    from salary
        where rank
            not in(select rank from ranks);

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

select salary.rank
    from salary left join ranks
        on salary.rank=ranks.rank
            where ranks.rank is null;

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

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

DROP TABLE if exists orders;
create table orders(
    customer varchar(30),
    whn date,
    totalitems int(11)
);
insert into orders values('jj','2010-10-10',5),
            ('jj','2010-10-11',3),
            ('jj','2010-10-12',1),
            ('aa','2010-10-10',5),
            ('bb','2010-10-10',8),
            ('cc','2010-10-10',10);

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

select customer,whn,totalitems
    from orders o1 where o1.totalitems=(
        SELECT max(totalitems)
            from orders o2
                where o1.customer = o2.customer
);

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

select o1.* from orders o1 join orders o2
    on(o1.customer=o2.customer)
        group by o1.customer
            having o1.totalitems=max(o2.totalitems
);