`

SQL SERVER 2005 中的CTE

阅读更多
SQL Server 2005中有一种新的语法叫做通用表表达式,CTE(Common Table Expression)。

这种语法的好处就是可以创建出一张临时的表,这张表可以在定义中使用自引用,使得我们处理父-子关系变得前所未有的方便.下面举例子
说明之.
USE AdventureWorks
GO

WITH MyCTE( ListPrice, SellPrice) AS
(
  SELECT ListPrice, ListPrice * .95
  FROM Production.Product
)

SELECT *
FROM MyCTE
这里就是罗列两列,将listprice*0.95.
当然,要用临时表的话也可以实现,如下
CREATE TABLE #MyCTE
(
  ListPrice money,
  SellPrice money
)

INSERT INTO #MyCTE
(ListPrice, SellPrice)
SELECT ListPrice, ListPrice * .95
FROM Production.Product  

但要注意的是,在声明CTE后,要立即调用,比如

USE AdventureWorks
GO
WITH MyCTE( ListPrice, SellPrice) AS
(
SELECT ListPrice, ListPrice * .95
FROM Production.Product
)
SELECT *
FROM Production.Location
SELECT *
FROM MyCTE
GO

将出错

    CTE最大的用处就是用在递归查询中了,CTE 引用它本身时,它被视为递归的。递归的 CTE 是根据至少两个查询(或者,用递归查询的说法,为成员)构建的。一个是非递归查询,也称为锚定成员 (AM)。另一个是递归查询,也称为递归成员 (RM)。查询由 UNION ALL 运算符分隔。以下示例显示了递归 CTE 的简化的一般形式:

WITH RecursiveCTE()
AS
(
  -- Anchor Member:
  -- SELECT query that does not refer to RecursiveCTE
  SELECT ...
  FROM
  ...
  UNION ALL
  -- Recursive Member
  -- SELECT query that refers to RecursiveCTE
  SELECT ...
  FROM
    JOIN RecursiveCTE

  ...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

在逻辑上,您可以将实现递归 CTE 的算法视为:

1.
 锚定成员被激活。集 R0(R 表示“结果”)被生成。
 
2.
 递归成员被激活,在引用 RecursiveCTE 时获得集 Ri(i = 步骤号)作为输入。集 Ri + 1 被生成。
 
3.
 步骤 2 的逻辑被反复运行(在每个迭代中递增步骤号),直到返回空集。
 
4.
 外部查询执行,在引用 RecursiveCTE 时,获得以前所有步骤的累积 (UNION ALL) 结果。 
 
举例子说明:

USE tempdb -- or your own test database
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  salary  money       NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT FK_Employees_mgrid_empid
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键,这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。
  比如要显示有关 Robert (empid=7) 及其所有级别下属的详细信息。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

  -- Anchor Member (AM)
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 7
  UNION ALL
 
  -- Recursive Member (RM)
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

empid       empname                   mgrid       lvl       
----------- ------------------------- ----------- -----------
7           Robert                    3           0         
11          David                     7           1         
12          Ron                       7           1         
13          Dan                       7           1         
14          James                     11          2    

lvl是递归的层数,而且是可以控制的,比如

WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2

以下为结果集:

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan  




 

分享到:
评论

相关推荐

    sqlserver 2005 使用CTE 实现递归

    ### SQL Server 2005 中使用 CTE 实现递归 #### 一、CTE 递归原理 在 SQL Server 2005 中,使用公共表表达式(Common Table Expression, CTE)实现递归是一种高效且直观的方式。递归 CTE 通常由两部分构成:定位...

    [sql server] SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL

    SQL Server 2005 中使用公用表表达式(CTE)简化嵌套 SQL SQL Server 2005 中的公用表表达式(CTE)是一种强大的工具,可以简化嵌套的 SQL 语句,提高代码的可维护性和性能。本文将介绍 CTE 的基本概念、语法和使用...

    SQL2005查询工具 SQLServer2005_SSMSEE

    描述中提到的"简单安装SQL软件可以下载使用",意味着SQL Server 2005 SSMSEE的安装过程相对简单,用户只需下载`SQLServer2005_SSMSEE.msi`这个安装文件,按照向导指示即可完成安装。安装后,用户无需复杂的配置就能...

    sql server 2005脚本

    在SQL Server 2005中,存储过程可以更灵活地处理输入参数,并支持输出参数。触发器则用于在数据修改(INSERT、UPDATE、DELETE)时自动执行特定操作。 3. **视图**: 视图是虚拟表,由一个或多个表的SELECT语句组成...

    SQL Server 2005 Beta 2 Transact-SQL 增强功能

    SQL Server 2005 Beta 2带来了对Transact-SQL的显著增强,这些改进主要集中在提升查询表达能力、数据完整性(DRI)支持、性能优化以及错误处理方面。以下是对这些增强功能的详细说明: 1. **排序函数**: SQL ...

    SQL SERVER的CTE用法

    在SQL Server中,公共表表达式(Common Table Expression,简称CTE)是一种非常有用的查询构造,它可以临时定义一个结果集,然后在这个结果集的基础上进行进一步的查询操作。CTE不仅可读性强,而且有助于简化复杂的...

    SQL SERVER 2005 简介

    SQL SERVER 2005 简介 SQL Server 2005 是一个全新的数据库平台系统,包含多种软件元件与服务,来满足企业对于日渐复杂的数据系统的需求。该版本的T-SQL结构化查询语言大幅扩增了新的查询指令,例如XQuery、取得...

    [sql server]SQL Server2005杂谈(2):公用表表达式(CTE)的递归调用.doc

    本文主要介绍了 SQL Server 2005 中公用表表达式(CTE)的递归调用,用于解决树型结构数据的查询问题。CTE 是 SQL Server 2005 中的一种新的查询方式,它可以简化复杂的查询过程。 在本文中,我们使用一个树型结构...

    Pro SQL Server 2005 (2006)

    2. **Transact-SQL (T-SQL)增强**:SQL Server 2005扩展了T-SQL语言,增加了新的函数、操作符和语句,如窗口函数、公用表表达式(CTE)和嵌套触发器,提高了开发人员编写复杂查询的灵活性。 3. **Integration ...

    Microsoft_SQL_Server_2005技术内幕:T-SQL查询.pdf

    本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...

    SQL Server2005分页查询

    在SQL Server 2005中,分页查询是一个常见的需求,特别是在处理大量数据时,为了提高用户体验,通常需要一次性加载一定数量的数据,而不是一次性显示所有数据。以下将详细讲解SQL Server 2005中分页查询的常用方法。...

    sql server2005教程

    在SQL Server 2005中,数据库的创建、维护和备份都有了新的工具支持。例如,使用SQL Server Management Studio (SSMS) 可以方便地进行数据库的设计、查询、备份和恢复。此外,SQL Server 2005还提供了对数据库镜像的...

    sqlserver驱动2012版

    标题中的“sqlserver驱动2012版”指的是SQL Server Native Client 2012,这是微软为SQL Server设计的一款数据库访问接口。SQL Server Native Client(简称SQLNCLI)是用于与SQL Server交互的一种客户端库,它包含了...

    SQL Server2005 安装(通用版)

    在本文中,我们将详细探讨SQL Server 2005的安装过程及其重要特性。 一、SQL Server 2005安装步骤 1. **准备工作**:确保你的计算机满足SQL Server 2005的系统需求,包括操作系统版本、内存、硬盘空间以及.NET ...

    SQLserver CTE With

    SQLserver CTE With 使用with产出临时表,在根据临时表二次查询

    SQL SERVER 2005 新增的几个小功能

    ### SQL Server 2005新增的小功能详解 #### 一、分页查询数据 在SQL Server 2005之前,实现分页查询通常较为复杂且效率不高。随着SQL Server 2005的发布,引入了一种更为简便的方式来实现分页查询,即通过`ROW_...

    SQL server Native Client 10.0

    这个组件在64位操作系统中尤其重要,因为它提供了对64位数据源的支持,确保了与SQL Server 2008 R2等64位数据库系统的高效通信。 1. **ODBC接口**: ODBC是SQL Server Native Client 10.0中的关键部分,它是一个...

    SqlServer2005课件

    Transact-SQL在Sql Server 2005中得到进一步扩展,支持窗口函数、公用表表达式(CTE)、并行查询等高级特性。这些功能让复杂的查询和分析变得更加便捷。 五、索引与查询优化 索引是提升查询速度的关键。Sql Server ...

    SQL Server 2005 T-SQL增强

    在SQL Server 2005中,T-SQL的增强主要体现在以下几个方面: 1. **窗口函数**:SQL Server 2005引入了窗口函数,如RANK(), DENSE_RANK(), ROW_NUMBER(),以及LEAD(), LAG()等,这些函数允许开发者在数据集上进行行...

    SQLSERVER2008中CTE的Split与CLR的性能比较

    在SQL Server 2008中,可以使用CTE来实现Split函数,通过递归地拆分字符串。这种方法相对于传统的基于游标的解决方案,效率已经有所提高。然而,CTE在处理大量数据时可能会遇到性能瓶颈,因为每次递归都会增加查询的...

Global site tag (gtag.js) - Google Analytics