T-SQLwith关键字的实例应用

　　Select字句在逻辑上是SQL语句最后进行处理的最后一步，所以，以下查询会发生错误：　　

成都创新互联公司专注于博野企业网站建设,自适应网站建设,商城网站建设。博野网站建设公司,为博野等地区提供建站服务。全流程按需网站建设，专业设计，全程项目跟踪，成都创新互联公司专业和态度为您提供的服务

 
 
  
  SELECT   
     
  　　YEAR(OrderDate) AS OrderYear,    
     
  　　COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM dbo.Orders    
     
  　　GROUP BY OrderYear;

　　因为group by是在Select之前进行的，那个时候orderYear这个列并没有形成。

　　如果要查询成功，可以像下面进行修改：

 
 
  
  　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID    
     
  　　FROM dbo.Orders) AS D    
     
  　　GROUP BY OrderYear;

　　还有一种很特殊的写法：

 
 
  
  　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM (SELECT YEAR(OrderDate), CustomerID    
     
  　　FROM dbo.Orders) AS D(OrderYear, CustomerID)    
     
  　　GROUP BY OrderYear;

　　在作者眼里，他是非常喜欢这种写法的，因为更清晰，更明确，更便于维护。

　　在查询中使用参数定向产生一批结果，这个技巧没有什么好说的。

　　嵌套查询，在处理逻辑上是从里向外进行执行的。

　　多重引用，有可能你的SQL语句包含了多次从一个表进行查询后进行连接组合。比如你要比较每年的顾客数同先前年的顾客数的变化，所以你的查询就必须JOIN了2个相同的表的实例，这也是不可避免的。

　　Common Table Expressions (CTE)

　　CTE是在SQL2005新加入的一种表的表示类型。

　　它的定义如下：

 
 
  
  　　WITH cte_name    
     
  　　AS   
     
  　　(    
     
  　　cte_query    
     
  　　)    
     
  　　outer_query_refferring to_cte_name;

　　注意：因为在标准的T-SQL语言中已经包含了WITH关键字，所以为了区分，CTE在语句的结尾加上了“;”作为停止符。

　　CTE实例一(结果集别名)

 
 
  
  　　WITH C AS   
     
  　　(    
     
  　　SELECT YEAR(OrderDate) AS OrderYear, CustomerID    
     
  　　FROM dbo.Orders    
     
  　　)    
     
  　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM C    
     
  　　GROUP BY OrderYear;

　　当然，作者本人有更推荐的写法：

 
 
  
  　　WITH C(OrderYear, CustomerID) AS   
     
  　　(    
     
  　　SELECT YEAR(OrderDate), CustomerID    
     
  　　FROM dbo.Orders    
     
  　　)    
     
  　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM C    
     
  　　GROUP BY OrderYear;

　　CTE实例二(多重CTEs)

 
 
  
  　　WITH C1 AS   
     
  　　(    
     
  　　SELECT YEAR(OrderDate) AS OrderYear, CustomerID    
     
  　　FROM dbo.Orders    
     
  　　),    
     
  　　C2 AS   
     
  　　(    
     
  　　SELECT OrderYear, COUNT(DISTINCT CustomerID) 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 CustomerID) AS NumCusts    
     
  　　FROM dbo.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 OUTER JOIN YearlyCount AS Prv    
     
  　　ON Cur.OrderYear = Prv.OrderYear + 1;

　　CTE实例四(修改数据)

　　1.把从customer表查询出来的结果，动态的组装进新表CustomersDups里：

 
 
  
  　　IF OBJECT_ID('dbo.CustomersDups') IS NOT NULL   
     
  　　DROP TABLE dbo.CustomersDups;    
     
  　　GO    
     
  　　WITH CrossCustomers AS   
     
  　　(    
     
  　　SELECT 1 AS c, C1.*    
     
  　　FROM dbo.Customers AS C1, dbo.Customers AS C2    
     
  　　)    
     
  　　SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol,    
     
  　　CustomerID, CompanyName, ContactName, ContactTitle, Address,    
     
  　　City, Region, PostalCode, Country, Phone, Fax    
     
  　　INTO dbo.CustomersDups    
     
  　　FROM CrossCustomers;

　　2.使用CTE移除数据，只保留CustomerDups表里同一CustomerID里KeyCol为最大的记录。

 
 
  
  　　WITH JustDups AS   
     
  　　(    
     
  　　SELECT * FROM dbo.CustomersDups AS C1    
     
  　　WHERE KeyCol <    
     
  　　(SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2    
     
  　　WHERE C2.CustomerID = C1.CustomerID)    
     
  　　)    
     
  　　DELETE FROM JustDups;

　　CTE实例五(对象容器)

　　即提供了封装的能力，有利于组件化的编程。作者额外的提醒，CTE无法直接内嵌，但是可以通过把CTE封装进一个对象容器里并从一个外部的CTE里对这容器的数据进行查询而实现内嵌。

　　作者也说明了，使用CTEs在VIEW和UDFs里是没有什么价值的。

　　有个例子，如下:

 
 
  
  　　CREATE VIEW dbo.VYearCnt    
     
  　　AS   
     
  　　WITH YearCnt AS   
     
  　　(    
     
  　　SELECT YEAR(OrderDate) AS OrderYear,    
     
  　　COUNT(DISTINCT CustomerID) AS NumCusts    
     
  　　FROM dbo.Orders    
     
  　　GROUP BY YEAR(OrderDate)    
     
  　　)    
     
  　　SELECT * FROM YearCnt;

　　CTE实例六(CTEs的递归)

　　作者给了一个例子，来讲述这个在SQL2005的新内容，CTEs的递归。

　　根据employeeId，返回此员工的信息，并包含所有下级员工的信息。(等级关系基于empolyeeId和reportsTo的属性)所返回的结果包含下列字段，employeeId,reportsTo,FirstName,LastName。

　　作者在这里，给予了一个最佳的索引方式：

 
 
  
  　　CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname    
     
  　　ON dbo.Employees(ReportsTo, EmployeeID)    
     
  　　INCLUDE(FirstName, LastName);

　　作者的解释：这个索引将通过一个单独的查询(局部扫描)来取得每个经理的直接下级。Include(FristName,LastName)加在这里，即是覆盖列。

　　小知识：什么Include索引?

　　Include索引是SQL2005的新功能。Include索引的列并不影响索引行的物理存储顺序，他们作为一个挂件‘挂在'索引行上。挂这些‘挂件'的目的在于，只需要扫描一把索引就获得了这些附加数据。

　　回到作者的例子上，下面是递归的代码：

 
 
  
  　　WITH EmpsCTE AS   
     
  　　(    
     
  　　SELECT EmployeeID, ReportsTo, FirstName, LastName    
     
  　　FROM dbo.Employees    
     
  　　WHERE EmployeeID = 5    
     
  　　UNION ALL   
     
  　　SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName    
     
  　　FROM EmpsCTE AS MGR    
     
  　　JOIN dbo.Employees AS EMP    
     
  　　ON EMP.ReportsTo = MGR.EmployeeID    
     
  　　)    
     
  　　SELECT * FROM EmpsCTE;

　　理解：一个递归的CTE包含了至少2个查询，第一个查询在CTE的身体里类似于一格锚点。这个锚点仅仅返回一个有效的表，并作为递归的一个锚。从上的例子看出来，锚点仅仅返回了一个employeeID = 5 的一行。然后的第2个查询是作为递归成员。当查询到下属成员的结果为空时，此递归结束。

　　如果你担心递归会造成永久循环，你可以使用下面的表达：

 
 
  
  　　WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);

　　默认的n为100，当n=0时，无限制。

原文出处：http://www.cnblogs.com/wwan/archive/2011/02/24/1964279.html

当前题目：T-SQLwith关键字的实例应用
网页地址：http://www.shufengxianlan.com/qtweb/news8/118758.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容