派生表

　　从这一篇开始要介绍表表达式了，什么是表表达式呢？表表达式是一种命名的查询表达式，代表一个有效的关系表。可以像其它表一样，在数据处理语句中使用表表达式。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.OrdersGROUP BY YEAR(orderdate);-- 分配列别名SELECT orderyear,COUNT(DISTINCT custid) AS numcusts FROM (SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders) AS Orders1GROUP 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 Orders1GROUP 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 Orders2WHERE numcusts>70;