T-SQL with关键字的实例应用

数据库
Select字句在逻辑上是SQL语句最后进行处理的最后一步但是比较容易出现查询错误,编者利用SQL2005新加入的一种表的表示类型——CTE来避免这种查询错误。

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

  1. SELECT 
  2.  
  3.   YEAR(OrderDate) AS OrderYear,  
  4.  
  5.   COUNT(DISTINCT CustomerID) AS NumCusts  
  6.  
  7.   FROM dbo.Orders  
  8.  
  9.   GROUP BY OrderYear;  
  10.  

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

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

  1.   SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
  2.  
  3.   FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
  4.  
  5.   FROM dbo.Orders) AS D  
  6.  
  7.   GROUP BY OrderYear;  
  8.  

  还有一种很特殊的写法:

  1.   SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
  2.  
  3.   FROM (SELECT YEAR(OrderDate), CustomerID  
  4.  
  5.   FROM dbo.Orders) AS D(OrderYear, CustomerID)  
  6.  
  7.   GROUP BY OrderYear;  
  8.  

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

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

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

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

  Common Table Expressions (CTE)

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

  它的定义如下:

  1.   WITH cte_name  
  2.  
  3.   AS 
  4.  
  5.   (  
  6.  
  7.   cte_query  
  8.  
  9.   )  
  10.  
  11.   outer_query_refferring to_cte_name;  
  12.  

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

  CTE实例一(结果集别名)

  1.   WITH C AS 
  2.  
  3.   (  
  4.  
  5.   SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
  6.  
  7.   FROM dbo.Orders  
  8.  
  9.   )  
  10.  
  11.   SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
  12.  
  13.   FROM C  
  14.  
  15.   GROUP BY OrderYear;  
  16.  

  当然,作者本人有更推荐的写法:

  1.   WITH C(OrderYear, CustomerID) AS 
  2.  
  3.   (  
  4.  
  5.   SELECT YEAR(OrderDate), CustomerID  
  6.  
  7.   FROM dbo.Orders  
  8.  
  9.   )  
  10.  
  11.   SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
  12.  
  13.   FROM C  
  14.  
  15.   GROUP BY OrderYear;  
  16.  

  CTE实例二(多重CTEs)

  1.   WITH C1 AS 
  2.  
  3.   (  
  4.  
  5.   SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
  6.  
  7.   FROM dbo.Orders  
  8.  
  9.   ),  
  10.  
  11.   C2 AS 
  12.  
  13.   (  
  14.  
  15.   SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
  16.  
  17.   FROM C1  
  18.  
  19.   GROUP BY OrderYear  
  20.  
  21.   )  
  22.  
  23.   SELECT OrderYear, NumCusts  
  24.  
  25.   FROM C2  
  26.  
  27.   WHERE NumCusts > 70;  
  28.  

  CTE实例叁(多重引用)

  1.   WITH YearlyCount AS 
  2.  
  3.   (  
  4.  
  5.   SELECT YEAR(OrderDate) AS OrderYear,  
  6.  
  7.   COUNT(DISTINCT CustomerID) AS NumCusts  
  8.  
  9.   FROM dbo.Orders  
  10.  
  11.   GROUP BY YEAR(OrderDate)  
  12.  
  13.   )  
  14.  
  15.   SELECT Cur.OrderYear,  
  16.  
  17.   Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts,  
  18.  
  19.   Cur.NumCusts - Prv.NumCusts AS Growth  
  20.  
  21.   FROM YearlyCount AS Cur  
  22.  
  23.   LEFT OUTER JOIN YearlyCount AS Prv  
  24.  
  25.   ON Cur.OrderYear = Prv.OrderYear + 1;  
  26.  

  CTE实例四(修改数据)

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

  1.   IF OBJECT_ID('dbo.CustomersDups'IS NOT NULL 
  2.  
  3.   DROP TABLE dbo.CustomersDups;  
  4.  
  5.   GO  
  6.  
  7.   WITH CrossCustomers AS 
  8.  
  9.   (  
  10.  
  11.   SELECT 1 AS c, C1.*  
  12.  
  13.   FROM dbo.Customers AS C1, dbo.Customers AS C2  
  14.  
  15.   )  
  16.  
  17.   SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol,  
  18.  
  19.   CustomerID, CompanyName, ContactName, ContactTitle, Address,  
  20.  
  21.   City, Region, PostalCode, Country, Phone, Fax  
  22.  
  23.   INTO dbo.CustomersDups  
  24.  
  25.   FROM CrossCustomers;  
  26.  

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

 

  1.   WITH JustDups AS 
  2.  
  3.   (  
  4.  
  5.   SELECT * FROM dbo.CustomersDups AS C1  
  6.  
  7.   WHERE KeyCol <  
  8.  
  9.   (SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2  
  10.  
  11.   WHERE C2.CustomerID = C1.CustomerID)  
  12.  
  13.   )  
  14.  
  15.   DELETE FROM JustDups;  
  16.  

  CTE实例五(对象容器)

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

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

  有个例子,如下:

  1.   CREATE VIEW dbo.VYearCnt  
  2.  
  3.   AS 
  4.  
  5.   WITH YearCnt AS 
  6.  
  7.   (  
  8.  
  9.   SELECT YEAR(OrderDate) AS OrderYear,  
  10.  
  11.   COUNT(DISTINCT CustomerID) AS NumCusts  
  12.  
  13.   FROM dbo.Orders  
  14.  
  15.   GROUP BY YEAR(OrderDate)  
  16.  
  17.   )  
  18.  
  19.   SELECT * FROM YearCnt;  
  20.  

  CTE实例六(CTEs的递归)

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

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

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

  1.   CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname  
  2.  
  3.   ON dbo.Employees(ReportsTo, EmployeeID)  
  4.  
  5.   INCLUDE(FirstName, LastName);  
  6.  

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

  小知识:什么Include索引?

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

  回到作者的例子上,下面是递归的代码:

  1.   WITH EmpsCTE AS 
  2.  
  3.   (  
  4.  
  5.   SELECT EmployeeID, ReportsTo, FirstName, LastName  
  6.  
  7.   FROM dbo.Employees  
  8.  
  9.   WHERE EmployeeID = 5  
  10.  
  11.   UNION ALL 
  12.  
  13.   SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName  
  14.  
  15.   FROM EmpsCTE AS MGR  
  16.  
  17.   JOIN dbo.Employees AS EMP  
  18.  
  19.   ON EMP.ReportsTo = MGR.EmployeeID  
  20.  
  21.   )  
  22.  
  23.   SELECT * FROM EmpsCTE;  
  24.  

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

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

  1.   WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);  
  2.  

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

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

 

责任编辑:艾婧 来源: 博客园
相关推荐

2010-07-20 13:52:27

SQL Server

2022-01-10 18:11:42

C语言应用技巧

2010-07-23 14:32:43

SQL Server

2010-11-12 14:49:28

SQL Server外

2009-06-29 12:58:47

This关键字java

2011-10-19 10:07:16

T-SQL查询变量

2023-08-15 08:26:34

SQL Server查找死锁

2021-10-13 06:49:13

SQL Server优化

2010-07-06 10:36:35

SQL Server

2010-10-19 16:06:26

SQL Server索

2023-11-10 09:29:30

MySQLExplain

2021-04-18 07:58:22

SQL Server数据库Apply

2010-02-02 14:27:54

C++ static关

2010-02-05 15:51:06

C++ explici

2022-02-17 08:31:38

C语言staic关键字

2021-02-01 13:10:07

Staticc语言UNIX系统

2022-05-06 08:32:40

Pythonwith代码

2024-03-15 15:12:27

关键字底层代码

2022-11-12 18:32:50

Golangomitemptyjson

2010-07-19 13:22:45

SQL Server
点赞
收藏

51CTO技术栈公众号