公用表表达式CTE表面上和派生表非常相似,看起来只是语义上的区别。但和派生表比较起来,CTE具有几个优势:第一,如果须要在一个CTE中引用另一个CTE,不需要像派生表那样嵌套,相反,只要简单地在同一个WITH子句中定义多个CTE,并用逗号把它们分隔开。每个CTE可以引用在它前面定义的所有CTE。而外部查询可以引用所有CTE。
下面是一个公用表表达式CTE的示例。
USE TSQLFundamentals2008; GO -- 公用表表达式CTE -- 一个简单的公用表表达式的例子 WITH USACusts AS ( SELECT custid,companyname FROM Sales.Customers WHERE country= N'USA' ) SELECT * FROM USACusts; -- 分配列别名 WITH C AS ( SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders ) SELECT orderyear, COUNT(DISTINCT custid) FROM C GROUP BY orderyear; -- 使用参数 DECLARE @empid AS INT=3; WITH C AS ( SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders WHERE empid= @empid ) SELECT orderyear, COUNT(DISTINCT custid) FROM C GROUP BY orderyear; -- 定义多个CTE WITH C1 AS ( SELECT YEAR(orderdate) AS orderyear, custid FROM Sales.Orders ), C2 AS ( SELECT orderyear,COUNT(DISTINCT custid) AS numcusts FROM C1 GROUP BY orderyear ) SELECT orderyear,numcusts FROM C2 WHERE numcusts>70; -- CTE的多引用 WITH YearlyCount AS ( SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts FROM Sales.Orders GROUP BY YEAR(orderdate) ) SELECT Cur.orderyear, Cur.numcusts AS curnumcusts,Prv.numcusts AS prvnumcusts,Cur.numcusts-Prv.numcusts AS growth FROM YearlyCount AS Cur LEFT JOIN YearlyCount AS Prv ON Cur.orderyear = Prv.orderyear+1;