SQL Server CTEs的语法与功能

数据库 SQL Server
我们今天主要向大家讲述的是SQL Server CTEs 的递归功能,CTEs是SQL Server数据库中的三种保存临时结果的主要操作方法之一。

以下的文章主要向大家讲述的是SQL Server CTEs 的递归功能是SQL Server数据库中的三种保存临时结果的实际操作方法之一。其另两种是临时表与View,当然你也可以说View并不保存数据,从这一点上来将, CTE更像View一些。

当你的查询需要从一个源表中统计出结果,基于这个结果再做进一步的统计,如此3次以上的话,你必然会用到View或者临时表,现在你也可以考虑用CTE了。

CTE的语法相当的简单, 如下:

With CTE的名字 AS

(

子查询

)

Select * from CTE的名字

SQL Server CTEs支持在定义时引用自身,从而可以达到递归的目的,看下面的例子(1):

  1. ---prepare test data   
  2. SET NOCOUNT ON;   
  3. CREATE TABLE dbo.Parts   
  4. (   
  5. partid INT NOT NULL PRIMARY KEY,   
  6. partname VARCHAR(25) NOT NULL   
  7. );   
  8. INSERT INTO dbo.Parts(partid, partname)   
  9. select 1, 'Black Tea'   
  10. union all select 2, 'White Tea'  
  11. union all select 3, 'Latte'  
  12. union all select 4, 'Espresso'  
  13. CREATE TABLE dbo.BOM   
  14. (   
  15. partid INT NOT NULL REFERENCES dbo.Parts,   
  16. assemblyid INT NULL REFERENCES dbo.Parts,   
  17. unit VARCHAR(3) NOT NULL,   
  18. qty DECIMAL(8, 2) NOT NULL,   
  19. UNIQUE(partid, assemblyid),   
  20. CHECK (partid <> assemblyid)   
  21. );   
  22. INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)   
  23. select 1, NULL, 'EA', 1.00  
  24. union all  
  25. select 2, 1, 'EA', 1.00  
  26. union all  
  27. select 3, 2, 'EA', 1.00  
  28. union all  
  29. select 4, 3, 'EA', 1.00  
  30. -- perform the test   
  31. WITH BOMTC AS(   
  32. SELECT assemblyid, partid   
  33. FROM dbo.BOM   
  34. WHERE assemblyid IS NOT NULL   
  35. UNION ALL   
  36. SELECT P.assemblyid, C.partid   
  37. FROM BOMTC AS P   
  38. JOIN dbo.BOM AS C ON C.assemblyid = P.partid   
  39. )   
  40. SELECT DISTINCT assemblyid, partid FROM BOMTC;   

输出结果如下:

例子(2):

  1. create table Employee   
  2. (   
  3. MgrId int,   
  4. EmpId int,   
  5. Title nvarchar(256)   
  6. )   
  7. insert into employee  
  8. select NULL, 1 ,'CEO'   
  9. union all  
  10. select 1, 2, 'VP'   
  11. union all  
  12. select 2, 3, 'Dev Manager'  
  13. union all  
  14. select 2, 4, 'QA Manager'   
  15. union all  
  16. select 1, 5, 'Sales Manager'   
  17. union all  
  18. select 3, 30, 'Developer'   
  19. union all  
  20. select 3, 31, 'Developer'   
  21. union all  
  22. select 4, 40, 'Tester'  
  23. union all   
  24. select 4, 41, 'Tester'   
  25. With DirectReports as   
  26. (   
  27. select MgrId, EmpId, Title, 0 as [Level] from Employee where MgrId is null   
  28. union all   
  29. select a.MgrId, a.EmpId, a.Title, [Level]+1 as [Level]   
  30. from Employee a join DirectReports b on a.MgrId=b.EmpId   
  31. )   
  32. select * from DirectReports   

结果:

讲解:重点是子查询中的两个select语句,以上述例子加以说明:

***个Select子句被称为锚点语句,它返回的结果跟普通的SQL没有区别,在这里返回MgrID为null的员工。

第二个子句就没那么普通了,它被称为递归语句,请注重到在from后面, Employee和DirectReport进行了链接操作。你一定会问,DirectReport的定义还没完成,这个名字代表什么结果呢?答案是它不只是代表了一个结果,实际上代表了一系列的结果。换句话说,在DirectReport这个名字下,包含着DirectReport0,DirectReport1,DirectReport2...这些较小的集合。

DirectReport0 是Employee和锚点结合的产物;

DirectReport1 是Employee和 DirectReport0 结合的产物;

依次类推, DirectReport n是Employee和DirectReport n-1结合的产物;

当DirectReport_n为空的时候,这个过程就结束了。

*** 锚点和DirectReport0,DirectReport1... 的并集就是DirectReport的内容。

作为一个程序员,每次看到递归的程序,必然会想到无限递归这个错误。为了避免了在开发阶段,无限递归导致数据库的崩溃,SQL Server提供了一个QueryHint, MaxRecursion,可以控制递归的***层数,假如超过这个数字而仍为结束,则视为代码错误,强制退出。如:Option(MaxRecursion 10)

可见SQL Server CTEs可以用来递归操作树形结构的数据表。

【编辑推荐】

  1. SQL Server浮点数据类型的详细解析
  2. 卸载SQL Server 2005组件的正确顺序
  3. SQL Server 2000删除实战演习
  4. SQL Server存储过程的命名标准如何进行?
  5.  Server数据库的临时表的正确操作步骤
责任编辑:佚名 来源: 人民邮电出版社
相关推荐

2010-09-27 10:59:23

SQL SERVER事

2010-11-09 10:10:08

SQL Server

2010-10-19 14:45:01

SQL SERVER临

2010-11-11 17:20:51

SQL Server创

2011-02-28 17:41:20

SQL Server

2010-11-11 10:18:59

select into

2010-07-19 13:22:45

SQL Server

2010-07-12 10:34:58

SQL Server视

2010-07-05 13:19:36

2009-03-23 09:11:36

SQL Server自SQL Server自SQL Server数

2010-07-20 09:15:03

SQL Server

2010-07-08 15:40:28

SQL Server嵌

2011-09-07 10:24:01

SQL Server镜像

2010-04-09 10:43:34

Oracle SQL

2010-07-09 15:39:29

SQL server

2010-07-19 14:24:15

SQL Server盘

2011-08-18 10:21:50

SQL ServerDATEPART

2010-07-14 13:21:19

SQL Server

2010-07-22 16:02:29

2011-04-18 13:02:08

SQL Server SQL Server
点赞
收藏

51CTO技术栈公众号