SqlServer2008实例11公共表表达式

    公共表表达式(CTE)和视图或衍生查询相似，允许创建一个临时的查询，该查询能在SELECT、INSERT、 UPDATE或DELETE查询范围中引用。和衍生查询不同，不需要在每次使用CTE的时候复制查询定义多次。同时也能在CTE定义中使用局部变量——这是在视图定义中所不能做到的。CTE的基本语法是：

WITH expression_name[(column_name)[,...n]] AS (CTE_query_definition) 参数描述 expression_name公共表表达式的名字(column_name)[,...n]表达式的唯一列名CTE_query_definition定义公共表表达式的SELECT查询

    非递归的CTE在查询中不引用自己。它就像查询的临时结果集。递归的CTE的定义和非递归的CTE差不多，只是递归的CTE返回层次化的以及和自身相关的数据．和其他方式相比，使用CTE来表示递归数据能尽量减少需要的代码量。     接下来的两个技巧会演示非递归的和递归的CTE．

1.使用非递归的公共表表达式

    这个公共表表达式的示例演示返回 Purchasing. Vendor表中的供应商——返回根据名字排序的前5个和最后5个结果：

use AdventureWorks go WITH VendorSearch(RowNumber,VendorName,AccountNumber) AS( SELECT ROW_NUMBER() OVER (ORDER BY Name) RowNum, Name, AccountNumber FROM Purchasing.Vendor ) SELECT RowNumber,VendorName,AccountNumber FROM VendorSearch WHERE RowNumber BETWEEN 1 AND 5 UNION SELECT RowNumber,VendorName,AccountNumber FROM VendorSearch WHERE RowNumber BETWEEN 100 AND 104

非递归的CTE也能像其他SELECT查询那样使用  

use AdventureWorks go WITH VendorSearch(VendorID,VendorName) AS( SELECT VendorID,Name FROM Purchasing.vendor ) SELECT v.VendorID,V.VendorName,p.ProductID,p.StandardPrice FROM VendorSearch v INNER JOIN Purchasing.ProductVendor p ON v.VendorID=p.VendorID ORDER BY v.VendorName

2.使用递归的公共表表达式

    在这个示例中，新的Company表定义了一个假想的巨大的联合企业。每一个公司都有--个CompanyID和可选的ParentComparlyID．这个示例会演示如何使用递归CTE在结果中显示公司的层次．首先，创建表：  

use AdventureWorks go CREATE TABLE dbo.Company (CompanyID INT NULL, ParentCompanyID INT NULL, CompanyName varchar(25) NOt NULL) INSERT dbo.Company(CompanyID,ParentCompanyID,CompanyName) VALUES (1,NULL,'Mega-Corp'), (2,1,'Mediamus-Corp'), (3,1,'KindaBigus-Corp'), (4,3,'GettinSnaller-Corp'), (5,4,'Smalles-Corp'), (6,5,'Puny-Corp'), (7,5,'Small2-Corp') go WITH CompanyTree(ParentCompanyID,CompanyID,CompanyName,CompanyLevel) AS ( SELECt parentCompanyID, CompanyID, CompanyName, 0 as CompanyLevel FROM dbo.Company WHERE ParentCompanyID IS NULL UNION ALL SELECT c.ParentCompanyID, c.CompanyID, c.CompanyName, p.CompanyLevel+1 FROM dbo.Company c INNER JOIN CompanyTree p ON c.ParentCompanyID=p.CompanyID ) SELECT ParentCompanyID,CompanyID,CompanyName,CompanyLevel FROM CompanyTree