34 派生表

34.1 派生表的定义

派生表是在外部查询的FROM子句中定义的，只要外部查询一结束，派生表也就不存在了。

 

34.2 派生表的作用

派生表可以简化查询，避免使用临时表。相比手动生成临时表性能更优越。派生表与其他表一样出现在查询的FROM子句中。

例如：

SELECT * FROM(
SELECT * FROM Customers WHERE 城市='广州')
AS Cus

其中Cus就是派生表。

 

34.3 派生表的特征

（1）所有列必须要有名称，出现无列名的要重命名。

（2）列名称必须是要唯一，相同名称肯定是不允许的。

（3）不允许使用ORDER BY（除非指定了TOP）。

注意：派生表是一张虚表，在数据库中并不存在，使我们自己创建的，目的主要是为了缩小数据的查找范围，提高查询效率。

 

34.4 派生表嵌套

如果需要用一个本身就引用了某个派生表的查询，去定义另一个派生表，最终得到的就是嵌套派生表。

例子：查询每年处理客户数超过70的订单年度和每年所处理的客户数量

方法一：不适用派生表

SELECT 
YEAR(orderdate) AS Orderyear,
COUNT(DISTINCT custid) AS Numcusts
FROM Sales.Orders
GROUP BY YEAR(Orderdate)
HAVING COUNT(DISTINCT Custid)>70;

 方法二：使用派生表

SELECT Orderyear,Numcusts
FROM (
SELECT Orderyear,COUNT(DISTINCT Custid) AS Numcusts
FROM (
SELECT YEAR(Orderyear) AS Orderyear,Custid
FROM Sales.Orders) AS D1
GROUP BY Orderyear
) AS D2
WHERE Numcusts>70;

嵌套查询看起来非常复杂，嵌套查询也是很容易产生问题的一个方面。在这个例子中，使用嵌套派生表的目的是为了重用列别名。但是，由于嵌套增加了代码的复杂性，所以对于本例考虑使用方案一。

 

34.5 批注

派生表在日常查询中经常使用到，用法也比较灵活，主要是执行效率会相对较高，因为它可以提前缩小查询范围。但是也不可乱用，特别是在不需要使用派生表而强制使用，效果反而会适得其反。