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 CGROUP 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 CGROUP BY orderyear;-- 定义多个CTEWITH 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 C2WHERE 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 growthFROM YearlyCount AS CurLEFT JOIN YearlyCount AS Prv ON Cur.orderyear = Prv.orderyear+1;