数据库的WITH子句与子查询

上一篇 / 下一篇 2017-01-02 00:37:19 / 个人分类：数据库

子查询可以简化SQL语句的编写，不过如果使用不当的话子查询会降低系统性能，为了避免子查询带来的性能问题，除了需要优化SQL语句之外还需要尽量降低使用子查询的次数。比如下面的子查询用来取得系统中所有年龄或者工资与Tom相同的人员：

SELECT * FROM T_Person

WHERE FAge=(SELECT FAge FROM T_Person WHERE FName='TOM')

OR FSalary=(SELECT FSalary FROM T_Person WHERE FName='TOM')

这个SQL语句可以完成要求的功能，不过可以看到类似的子查询被用到了两次，这会带来下面的问题：

l 同一个子查询被使用多次会造成这个子查询被执行多次，由于子查询是比较消耗系统资源的操作，所以这会降低系统的性能。

2、同一个子查询在多处被使用，这违反了编程中的DRY（Don't Repeat Yourself）原则，如果要修改子查询就必须对这些子查询同时修改，很容易造成修改不同步。

造成这种问题的原因就是子查询只能在定义的时候使用，这样如果多次使用就必须多次定义，为了解决这种问题，SQL提供了WITH子句用于为子查询定义一个别名，这样就可以通过这个别名来引用这个子查询了，也就是实现“一次定义多次使用”。

使用WITH子句来改造上面的SQL语句：

WITH person_tom AS

(

SELECT * FROM T_Person

WHERE FName='TOM'

)

SELECT * FROM T_Person

WHERE FAge=person_tom.FAge

OR FSalary=person_tom.FSalary

可以看到WITH子句的格式为：

WITH 别名AS

(子查询)

定义好别名以后就可以在SQL语句中通过这个别名来引用子查询了，注意这个语句是一个SQL语句，而非存储过程，所以可以远程调用。还可以在WITH语句中为子查询中的列定义别名，定义的方式就是在子查询别名后列出参数名列表。如下：

WITH person_tom(F1,F2,F3) AS

(

SELECT FAge,FName,FSalary FROM T_Person

WHERE FName='TOM'

)

SELECT * FROM T_Person

WHERE FAge=person_tom.F1

OR FSalary=person_tom.F3

以上摘自《程序员的SQL金典》