来自 数据库 2019-11-23 07:21 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

SQL递归查询(SqlServer/ORACLE递归查询)[语法差异分析]

主要分析一下用CTE的递归操作:

-- author:jc_liumangtu(【DBA】小七)
-- date:    2010-03-30 15:09:42
-- version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
--     Oct 14 2005 00:33:37
--     Copyright (c) 1988-2005 Microsoft Corporation
--     Developer Edition on Windows NT 5.1 (Build 2600: Service Pack

老规矩先建表:复制代码 代码如下: CREATE TABLE [dbo].[Co_ItemNameSet]( [ItemId] [int] NULL, [ParentItemId] [int] NULL, [ItemName] [nchar] COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] 插入数据:复制代码 代码如下: --给表插入数据 insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values insert into dbo.Co_ItemNameSet values 查询插入的数据:复制代码 代码如下:--查询数据 select * from Co_ItemNameSet 结果图:

3)


use test
set nocount on
if object_id('Dept','U') is not null
drop table Dept
go
create table Dept(ID int,ParentID int,Name varchar(20))  
insert into Dept select 1,0,'AA'
insert into Dept select 2,1,'BB'
insert into Dept select 3,1,'CC' 
insert into Dept select 4,2,'DD' 
insert into Dept select 5,3,'EE' 
insert into Dept select 6,0,'FF'
insert into Dept select 7,6,'GG'
insert into Dept select 8,7,'HH'
insert into Dept select 9,7,'II'
insert into Dept select 10,7,'JJ'
insert into Dept select 11,9,'KK'

go  
SELECT * FROM Dept;

--查询树状结构某节点的上级所有根节点。
with cte_root(ID,ParentID,NAME)
as
网上澳门金莎娱乐,(
    --起始条件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II'   --列出子节点查询条件
    union all
    --递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_root b          --执行递归,这里就要理解下了
    on a.ID=b.ParentID  --根据基础表条件查询子节点(a.ID),通过CTE递归找到其父节点(b.ParentID)。
)                       --可以和下面查询子节点的cte_child对比。
select * from cte_root ;

--查询树状结构某节点下的所有子节点。
with cte_child(ID,ParentID,NAME)
as
(
    --起始条件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II' --列出父节点查询条件
    union all
    --递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_child b
    on ( a.ParentID=b.ID)  --根据查询到的父节点(a.Parent),通过CTE递归查询出其子节点(b.ID)
)

select * from cte_child --可以改变之前的查询条件'II'再测试结果

ID          ParentID    Name


1           0           AA
2           1           BB
3           1           CC
4           2           DD
5           3           EE
6           0           FF
7           6           GG
8           7           HH
9           7           II
10          7           JJ
11          9           KK

ID          ParentID    NAME


9           7           II
7           6           GG
6           0           FF

ID          ParentID    NAME


9           7           II
11          9           KK

复制代码

网上澳门金莎娱乐 1)

在msdn中介绍了CTE的一些限制:

至少有一个定位点成员和一个递归成员,当然,你可以定义多个定位点成员和递归成员,但所有定位点成员必须在递归成员的前面
定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符,最后一个定位点成员与递归成员之间必须使用UNION ALL,递归成员之间也必须使用UNION ALL连接
定位点成员和递归成员中的字段数量和类型必须完全一致
递归成员的FROM子句只能引用一次CTE对象
递归成员中不允许出现下列项
    SELECT DISTINCT
    GROUP BY
    HAVING
    标量聚合
    TOP
    LEFT、RIGHT、OUTER JOIN(允许出现 INNER JOIN)
    子查询

接下来介绍下Oracle里面的递归查询方法,connect by prior ,start with。相对于SqlServer来说,Oracle的方法更加简洁明了,简单易懂。很容易就让人理解其用法。借来我会用和上面SqlServer同样的数据和结构进行代码演示,和对一些关键字的用法进行阐述。

SELECT …..

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH];

下面是代码测试:

网上澳门金莎娱乐 2)

网上澳门金莎娱乐 3代码

--创建表
create table Dept(ID int,ParentID int,Name varchar(20));
--增加测试数据,和上面的SqlServer数据相同
insert into Dept  select 1,0,'AA' from dual;
insert into Dept  select 2,1,'BB' from dual;
insert into Dept  select 3,1,'CC'  from dual;
insert into Dept  select 4,2,'DD'  from dual;
insert into Dept  select 5,3,'EE'  from dual;
insert into Dept  select 6,0,'FF' from dual;
insert into Dept  select 7,6,'GG' from dual;
insert into Dept  select 8,7,'HH' from dual;
insert into Dept  select 9,7,'II' from dual;
insert into Dept  select 10,7,'JJ' from dual;
insert into Dept  select 11,9,'KK' from dual;
commit;

--查询根节点(父节点)
select * from Dept            --查询基础表
connect by id=prior parentid  --connect by就是字段的关联关键字,prior有预先和前的意思,则是放在哪个字段前,哪个就是递归的上一层
start with name='II';         --start with则是递归的起始位置,也可以用id或者是parentid。可以修改II的值测试其他数据。

--查询结果
ID    PARENTID    NAME
9      7            II
7      6            GG
6      0            FF

--查询子节点

select * from Dept
connect by prior id=parentid  --同样的语句,仅仅改变prior位子,就发生了指向性的变化,就是这里id为递归上一层。
start with name='II';

--查询结果
ID    PARENTID    NAME
9       7            II
11    9            KK

--测试结果和SqlServer一致,语句却更精练,简洁易懂。

复制代码

网上澳门金莎娱乐 4)

经过分别对SqlServer和Oracle的测试,发现两个数据库都很好的支持递归查询,相比之下Oracle的递归查询语句更加简练易懂,更容易让人理解。

在做测试的时候,SqlServer更方便的产生测试数据,上面的代码可以复制后重复执行,而Oracle复制执行一次可以,重复执行的话,在执行创建表的工作,就会报错了,原因很简单,Oracle要判断表存在然后删除后重建的工作用代码实现很麻烦。而SqlServer只需要if后drop表再create就搞定。所以两种数据库各有千秋。

  1. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。

  2. 不能在 CTE_query_definition 中使用以下子句:复制代码 代码如下: COMPUTE 或 COMPUTE BY ORDER BY INTO 带有查询提示的 OPTION 子句 FOR XML FOR BROWSE 6. 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,如下面的SQL所示:复制代码 代码如下:declare @s nvarchar set @s = '测试%'; -- 必须加分号 with t_tree as ( select * from 表 where 字段 like @s ) select * from t_tree------------------------------------操作------------------------------------

网上澳门金莎娱乐 5)

递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集,所以它们被称为“定位点成员”。

在 SQLSERVER2005以后,mssql开始有了递归查询的方法了。比较起最开始写存储过程或者写function的方式。这样的方式更加简便灵活的。

将 CTE 表达式拆分为定位点成员和递归成员。

网上澳门金莎娱乐 6代码

例程的调用。

而oracle也有自带的树形结构递归查询方法,connect by

关于使用CTE的递归查询----SQL Server 2005及以上版本

下面我自己写的一段SQL,简单注释下CTE共用表达式的一些用法。 实现对树状结构的根节点和子节点的查询。

递归 CTE 由下列三个元素组成:


上面可能对with as说的有点儿啰嗦了,下面进入正题:

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中,递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。

例程的递归调用。

返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。

公用表表达式 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:SQL递归查询(SqlServer/ORACLE递归查询)[语法差异分析]

关键词: