派生表

　　从这一篇开始要介绍表表达式了，什么是表表达式呢？表表达式是一种命名的查询表达式，代表一个有效的关系表。可以像其它表一样，在数据处理语句中使用表表达式。SQL Server支持四种类型的表表达式：派生表，公用表表达式，视图和内联表值函数。

　　派生表(也称为表子查询)是在外部查询的FROM子句中定义的。派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表就不存在了。定义派生表的查询语句要写在一对圆括号内，后面跟着AS子句和派生表的名称。

　　下面是派生表的示例代码。

USE TSQLFundamentals2008;
GO

-- 派生表
-- 一个简单的派生表示例
SELECT * 
FROM (SELECT custid,companyname FROM Sales.Customers WHERE country= N'USA') AS USACusts;

-- 原来写法
SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts 
FROM Sales.Orders
GROUP BY YEAR(orderdate);

-- 分配列别名
SELECT orderyear,COUNT(DISTINCT custid) AS numcusts 
FROM (SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders) AS Orders1
GROUP BY orderyear;

-- 使用参数
DECLARE @empid AS INT=3;

SELECT orderyear,COUNT(DISTINCT custid) AS numcusts 
FROM (SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders WHERE empid= @empid) AS Orders1
GROUP BY orderyear;

-- 嵌套
SELECT orderyear, numcusts 
FROM (SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
        FROM (SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders) AS Orders1
        GROUP BY orderyear) AS Orders2
WHERE numcusts>70;