SQL Server 2005 Beta 2 Transact-SQL 增强功能(一)

视图

CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN VEmpOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E.ManagerID = OM.EmployeeID

派生表

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E

JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ManagerID = OM.EmployeeID

CTE

WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN EmpOrdersCTE AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E.ManagerID = OM.EmployeeID
The CTE''s definition must be followed by an outer query, which may or may not refer to it.
You cannot refer to the CTE later in the batch after other intervening statements.

您可以在同一 WITH 子句中定义多个 CTE，每一个都引用先前定义的 CTE。逗号用来分隔各个 CTE。例如，假设您希望计算雇员定单数量的最小值、最大值以及二者之间的差值：

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader

GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

以下为结果集：

MN MX Diff
----------- ----------- -----------
160 400 240

在 EmpOrdersCTE 中，计算每个雇员的定单数量。在 MinMaxCTE 中，引用 EmpOrdersCTE 以计算雇员定单数量的最小值、最大值以及二者之间的差值。

注 在 CTE 内部，您并非只能引用恰好在它前面定义的 CTE;相反，您可以引用之前定义的所有 CTE。请注意，不允许向前引用：CTE 可以引用在它前面定义的 CTE 和它本身（参阅后文中的递归查询），但是不能引用在它后面定义的 CTE。例如，如果您在同一 WITH 语句中定义了 CTE C1、C2、C3，则 C2 可以引用 C1 和 C2，但是不能引用 C3。

在另一个示例中，以下代码生成一个直方图，以计算位于最小值和最大值之间的四个定单数量范围内的雇员数量。如果这些计算对您似乎很复杂，则请不要花费时间来试图搞懂它们。该示例的目的是使用实际方案来演示如何在同一 WITH 语句中声明多个 CTE（其中每一个都可能引用前面的 CTE）。

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),

MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
),
NumsCTE(Num)
AS
(
SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
),
StepsCTE(Step, Fromval, Toval)
AS
(
SELECT
Num,
CAST(MN + ROUND((Num-1)*((Diff+1)/4.), 0) AS INT),
CAST(M