子查询转化作连接查询的例子

子查询转化为连接查询的例子
Sql代码 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); 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为每一个等级的定的薪资标准了。

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

Sql代码  select payment from salary        where rank=(           SELECT rank from ranks                where title=(                   SELECT title from jobs                        where employee='张三')   );  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

最后得到如下结果：

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

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

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

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

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

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

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

Sql代码    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);  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，以下是最容易想到的查询方式：

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

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

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

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

查看更多 下一篇