浅谈T-SQL中的派生表和CTE

引言

　　表表达式是一种命名的查询表达式，代表一个有效的关系表。MSSQL支持4种类型的表表达式，它们分别是：派生表、公用表表达式（CTE）、视图以及内联表值函数。表表达式并不是物理上真实存在的对象，它们是虚拟的。对于表表达式的查询在数据库引擎内部都会转化为底层对象的查询。使用表表达式通常是体现在代码的逻辑方面，而不是性能方面。下面就让我妈来详细介绍每一种类型吧。

派生表

　　派生表也叫表子查询是在外部查询的FROM子句中定义的。派生表的存在范围是定义它的外部查询，只要外部查询一结束，派生表也就不存在了。下面我们来看一个使用派生表的例子。

　　假设需要返回所有的美国客户，使用派生表我们可以这样使用。

select custid,companyname from
    (select custid,companyname from
        Sales.Customers
     where country=N'USA') as USACusts

　　我们看到了派生表的简单例子。要有效的定义任何类型的表表达式，必须满足下面三个条件。

　　不保证有一定的顺序

　　表表达式代表的是一个表，关系表的中的行是没有固定顺序的。所以表表达式不允许使用ORDER BY子句。唯一例外是在T-SQL中使用了TOP选项，在带有TOP选项的查询语句中，ORDER BY子句只有一个目的，那就是为TOP选项定义要筛选出哪些行，不用于通常数据显示的目的。表表达式也符合这一法则。

　　所有的列必须有名称

　　所有的列名必须唯一

派生表的多引用

　　我们知道派生表是在外部查询的FROM子句中定义的，其逻辑处理顺序并不优先于外部查询。当对外部查询的FROM子句进行处理时，派生表其实并不存在。因此，如果我们要引用派生表的多个实例时，必须基于同一查询去定义多个派生表。

　　假设我们需要对上面的例子进行自联接，使用派生表就会显得代码冗余，如下：

use insidetsql2008
select * from
    (select custid,companyname from
        Sales.Customers
     where country=N'USA') as USACusts
inner join (select custid,companyname from
                Sales.Customers
            where country=N'USA') as USACusts1
on USACusts.custid=USACusts1.custid

　　我们看到其实我们是对同一张表进行联接，但是需要将代码定义多遍，这无疑造成代码的混乱和冗余。

 公用表表达式（CTE）

　　公用表表达式是和派生表很相似的一种表表达式，与派生表相比，CTE有一些重要的优势。下面还是通过一个简单的例子来看学习CTE吧

　　还是最上面查询所有美国客户例子。

with USACusts as (
    select custid,companyname from
        Sales.Customers
     where country=N'USA'
)select * from USACusts

　　和派生表一样，一旦外部查询结束，CTE的生命周期也就结束了。

　　我们知道派生表在多引用的情况下，会造成代码冗余，使用公用表表达式可以缓解这方面的问题，我们来看一个例子（还是上面派生表的那个例子）。

with USACusts as (
    select custid,companyname from
        Sales.Customers
     where country=N'USA'
)
select * from USACusts
inner join USACusts as USACusts1
on USACusts.custid=USACusts1.custid

　　我们看到使用公用表表达式我们无需像派生表那样定义相同的查询语句，只需要将查询语句定义一边，然后可以进行引用即可。