SQL语句-递归

2023-11-09 来源：六九路网

WITH UserID (OrgCode, ParentCode, Level) AS (

SELECT OrgCode,ParentCode,0 AS Level FROM UMS_OrganizationWHERE OrgCode= ‘ORG12743‘ UNION ALL SELECT e.OrgCode,e.ParentCode, Level + 1 FROM UMS_Organization AS e INNER JOIN UserID AS d ON e.ParentCode = d.OrgCode ) SELECT OrgCode FROM UserID

http://www.cnblogs.com/xqhppt/archive/2011/02/15/1955366.html

SQL语句-递归

标签：

小编还为您整理了以下内容，可能对您也有帮助：

sql用什么方法可以实现递归函数？

在 SQL 中，你可以使用递归查询来实现递归函数。递归查询是一种查询，其中结果集由一条或多条 SELECT 语句和一条用于查找下一级行的 UNION ALL 语句组成。

例如，假设你有一张表，其中包含父子关系的信息（即，每个记录都有一个父级 ID，表示它的父级），你可以使用以下递归查询来查询每个记录的所有祖先：

WITH RECURSIVE ancestors AS (

-- 初始查询

SELECT id, parent_id

FROM your_table

WHERE id = :your_id

UNION ALL

-- 递归查询

SELECT t.id, t.parent_id

FROM your_table t

INNER JOIN ancestors a ON t.id = a.parent_id

)

SELECT id FROM ancestors;

在这个查询中，我们使用了一个递归关系，其中第一个 SELECT 语句是初始查询，用于查询给定 ID 的记录。第二个 SELECT 语句是递归查询，用于查询与当前记录的父级相关的记录。通过将这两个 SELECT 语句用 UNION ALL 连接起来，我们就可以获得所有祖先的列表了。

sql 怎么递归查询的方法：

1．创建测试表，createtabletest＿connect（idnumber，p＿idnumber）；

2．插入测试数据，

Insertintotest＿connectvalues（1，1）；

Insertintotest＿connectvalues（2，1）；

Insertintotest＿connectvalues（3，2）；

Insertintotest＿connectvalues（4，3）；

提交；

3．查询数据表的内容，选择＊fromtest＿connect，

4．执行递归查询语句，将答案添加到nocycle元素中，就不会有［ora－01436：CONNECTBYerrorintheuserdata］。执行结果如下：

Select＊

来自test＿connectt

从id＝4开始

由nocyclepriort连接。p＿id＝t．i．

sql语句实现递归查询所有节点,mysql和oracle都能用的

更多相关免费学习推荐：mysql教程(视频)

mysql查询子节点的方法：

创建menu表：

CREATE TABLE `menu` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id', `parent_id` int(11) DEFAULT NULL COMMENT '父节点id', `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称', `menu_url` varchar(128) DEFAULT '' COMMENT '菜单路径', `status` tinyint(3) DEFAULT '1' COMMENT '菜单状态 1-有效；0-无效', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `menu` VALUES ('0', null, '菜单0', ' ', '1');INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');

得到的目录结构如下图所示：

查询

先贴出sql语句：

select id from ( select t1.id, if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild from ( select id,parent_id from re_menu t where t.status = 1 order by parent_id, id ) t1, (select @pids := 要查询的菜单节点 id) t2 ) t3 where ischild != 0

比如，要查询菜单节点12的所有子节点,则查处的结果为：

sql语句实现递归查询所有节点,mysql和oracle都能用的

更多相关免费学习推荐：mysql教程(视频)

mysql查询子节点的方法：

创建menu表：

CREATE TABLE `menu` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id', `parent_id` int(11) DEFAULT NULL COMMENT '父节点id', `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称', `menu_url` varchar(128) DEFAULT '' COMMENT '菜单路径', `status` tinyint(3) DEFAULT '1' COMMENT '菜单状态 1-有效；0-无效', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `menu` VALUES ('0', null, '菜单0', ' ', '1');INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');

得到的目录结构如下图所示：

查询

先贴出sql语句：

select id from ( select t1.id, if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild from ( select id,parent_id from re_menu t where t.status = 1 order by parent_id, id ) t1, (select @pids := 要查询的菜单节点 id) t2 ) t3 where ischild != 0

比如，要查询菜单节点12的所有子节点,则查处的结果为：

SQL数据库实现递归查询的几种代码方法

SQL数据库实现递归查询的几种代码方法表结构

ProctCategory

CategoryID Level ParentCategoryID

数据

T SQL

WITH CategoryTemp(CategoryID ParentCategoryID) 临时表用来保存查到的Category

(

SELECT CategoryID ParentCategoryID FROM ProctCategory WHERE ParentCategoryID<= 将所有的第一层查出来作为初始数据需要查第几层或者哪个ParentCategoryID下面所有的 N层把ParentCategoryID赋相关的值即可

UNION ALL 查询N层

SELECT pc CategoryID ParentCategoryID FROM ProctCategory pc

LEFT JOIN CategoryTemp ct ON pc ParentCategoryID=ct CategoryID

WHERE ParentCategoryID> 因为第一层前面已经查出来了所以这里把第一层筛选掉

)

SELECT CategoryID ParentCategoryID FROM CategoryTemp

结果

如果把ParentCategoryID赋为结果则为

实例

ID　是否为部门部门名上级ID y 部门 y 部门 n 张三 n 李二 y 部门 n 王五 y 部门3　 n 小三我想找询 ID 值为下级的所有人员包括下级部门的所有人员

创建查询函数 create function f_id( @id int 要查询的id )returns @re table(id int level int) as begin declare @l int set @l= insert @re select id @l from 表 where 上级id=@id while @@rowcount> begin set @l=@l+ insert @re select a id @l from 表 a join @re b on a 上级id=b id and b level=@l end return end go

调用函数进行查询 select a * from 表 a join f_id( ) b on a id=b id

联合查询

测试数据 create table 表(ID int 是否为部门 char( ) 部门名 varchar( ) 上级ID int) insert 表 select y 部门 union all select y 部门 union all select n 张三 union all select n 李二 union all select y 部门 union all select n 王五 union all select y 部门 union all select n 小三 go

调用函数进行查询 select a * from 表 a join f_id( ) b on a id=b id go

删除测试 drop table 表 drop function f_id

/* 测试结果

ID 是否为部门部门名上级ID n 小三

lishixin/Article/program/MySQL/201311/29557

SQL数据库实现递归查询的几种代码方法

SQL数据库实现递归查询的几种代码方法表结构

ProctCategory

CategoryID Level ParentCategoryID

数据

T SQL

WITH CategoryTemp(CategoryID ParentCategoryID) 临时表用来保存查到的Category

(

UNION ALL 查询N层

SELECT pc CategoryID ParentCategoryID FROM ProctCategory pc

LEFT JOIN CategoryTemp ct ON pc ParentCategoryID=ct CategoryID

WHERE ParentCategoryID> 因为第一层前面已经查出来了所以这里把第一层筛选掉

)

SELECT CategoryID ParentCategoryID FROM CategoryTemp

结果

如果把ParentCategoryID赋为结果则为

实例

ID　是否为部门部门名上级ID y 部门 y 部门 n 张三 n 李二 y 部门 n 王五 y 部门3　 n 小三我想找询 ID 值为下级的所有人员包括下级部门的所有人员

调用函数进行查询 select a * from 表 a join f_id( ) b on a id=b id

联合查询

调用函数进行查询 select a * from 表 a join f_id( ) b on a id=b id go

删除测试 drop table 表 drop function f_id

/* 测试结果

ID 是否为部门部门名上级ID n 小三

lishixin/Article/program/MySQL/201311/29557

sql存储过程用递归

表结构：
sale_area销售分区表
PK#area_id
class_id
gonghao

prod_class产品分类表
PK#class_id
class_name

问题sql语句：
select distinct pa.class_id from prod_class As pa,prod_class As pb,sale_area where pb.class_id in(select sale_area.class_id from sale_area where sale_area.gonghao=<param>) and pa.class_id like pb.class_id||'%'

问题定义：
根据传入的参数gonghao来确定对应的class_id集，然后遍历这个class_id集，并“扩充”这个集合
要理解“扩充”必须知道class_id是这样设等级：
0为最高级
|_01
| |_011
| |_012
|_02
|_011
|_012
那么拥有等级0的话，所有的等级都应该可以访问，拥有等级01的话，011、012也可以访问。
这句sql的目的就是得到当前员工能访问的所有产品分类及它的子集。

通过自连接的做法在Oracle里是对的，在DB2里就错，原因在于DB2中谓词like两边至少有一个是字符串，也就是说DB2不认为pb.class_id||'%'是字符串。

求解：
要完成上面的任务有什么可替代的办法或改进的地方，注意环境是DB2 UDB 7.2。
---------------------------------------------------------------

就是树的问题。
使用公共表达式可以完成。
你在信息中心里面按recusion去搜索，可以找到递归sql的写法。
---------------------------------------------------------------

问题写的很清楚，我喜欢！
如果要使用DB2的公共表达式建议你把
prod_class产品分类表结构改一下
PK#class_id
class_name
Parent_Class_id

sql语句如下：

with temp_class_id( class_id,class_name)
(
select pc.class_id,pc.class_name from prod_class pc
where pc.class_id in (select sale_area.class_id from sale_area
where sale_area.gonghao=<param>)
union
select pc.class_id,pc.class_name from prod_class pc,
temp_class_id tc
where pc.parent_id = tc.class_id)
select * from temp_class_id;

sql存储过程用递归

sql语句递归

这个查询需要知道你的表a中各个部门的关系，例如我们单位的库中就是这样的：
select * from a；
部门id 部门名称上级部门id 上级部门名称
0000 董事会 0000 董事会
1000 财务部 0000 董事会
1001 会计室 1000 财务部
2000 销售部 0000 董事会
2001 华东区销售分部 2000 销售部
2002 华北区销售分部 2000 销售部
。。。。。。。。。
如果和我们这里相同的话就可以用
SELECT SUM(B.盈利) FROM B WHERE b.id in (select a.部门id from a where substr(a.部门id,0,1)='2');
来查询出销售部的盈利。

sql语句递归

SQL递归查询知多少

1、基本概念

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。公用表表达式可以包括对自身的引用，这种表达式称为递归公用表表达式。

创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。

在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。

启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。

在同一语句中多次引用生成的表。

MSDN上对CTE的介绍

T-SQL查询进阶--详解公用表表达式(CTE)

CTE 的基本语法结构如下：

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

--只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

--运行 CTE 的语句为：

SELECT <column_list> FROM expression_name;

即三个部分：

公用表表达式的名字（在WITH关键字之后）

查询的列名（可选）

紧跟AS之后的SELECT语句（如果AS之后有多个对公用表的查询，则只有第一个查询有效）

如何用一段SQL语句递归找到一个目录下的所有子目录

有一个存储目录树的表,定义目录间的关系字段有两个:
如下:(ClassID表示该目录的编号,ClassParentID表示它的父目录的编号)

ClassID ClassParentID
1 1
2 1
3 2
4 3
5 3

表示的关系应该是

-1--(根目录)
|
-2--(第二级目录)
|
-3--(第*目录)
|
-4--(第四级目录)
|
-5--(第四级目录)

这样的目录关系.

那么我通过知道一个目录的ClassID,如何通过一条SQL语句找到所有该目录的子目录?

如:

"SELECT * FROM 表 WHERE ClassParentID = "+ClassID+"and ClassParentID <>"+ClassID

但只能找到第一层的子目录,如何找到所有子目录,请大家给一条SQL语句实现!!

(因为事先不知道目录有多少层,当然一定是一条带有while循环的SQL语句)

1、

create table Test2
(
ClassID int,
ClassParentID int,
)
go

create proc Test2_proc
@nClassParentID int -- 要查询的父节点ID 号
as
begin
set nocount on
--
declare @nClassID int,
@nIndex int
-- create TempTable
create table #ClassIDTemp
(
nIndex int identity(1,1),
ClassID int,
ClassParentID int,
nFlag int default(0),
)
create index #ClassIDTemp_Index on #ClassIDTemp(nIndex)

-- insert First RecordSet

insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassParentID order by ClassParentID
-- Circle
while(0=0)
begin

select top 1 @nIndex = nIndex, @nClassID = ClassID
from #ClassIDTemp where nFlag = 0 order by nIndex
asc
if @@rowcount = 0
break
else
begin

insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassID order by ClassParentID
end
update #ClassIDTemp set nFlag = 1 where nIndex = @nIndex
end

-- Select Result
select ClassID,ClassParentID from #ClassIDTemp order by nIndex
if exists(select * from sysobjects where name = '#ClassIDTemp')
drop table #ClassIDTemp
end
go

2、

declare @Table table ( ClassID int primary key , ClassParentID int)
declare @Test table ( ClassID int primary key , ClassParentID int)

insert into @Table values ( 1, 0 ) -- 建议这里是 1，0 而不是 1， 1
insert into @Table values ( 2, 1 )
insert into @Table values ( 3, 2 )
insert into @Table values ( 4, 3 )
insert into @Table values ( 5, 3 )

insert into @Test select * from @Table where ClassID = 2

while exists ( select * from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test ) )
begin
insert into @Test
select A.* from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test )
end

select * from @Test

3、

sql1 = "(SELECT ClassID FROM 表 WHERE ClassParentID =" + 变量 + ")"

sql2 = "(SELECT ClassID FROM 表 WHERE (ClassParentID IN
"+ sql1 + ") OR (ClassID IN " + sql1 + "))"

sql = "SELECT * FROM 表 WHERE ClassParentID IN " + sql2 + " OR ClassID IN " + sql2

这个办法可实现1到4级的所有情况（包括只有2级、3级的情况）

4、--我的方法是用自定义函数实现,这样具有通用性

--测试

--测试数据
create table 表(ClassID int,ClassParentID int)
insert 表 select 1,0
union all select 2,1
union all select 3,2
union all select 4,3
union all select 5,3
go

--自定义函数,返回指定目录下的所有子目录列表
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 --直接用@@rowcount判断
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go

--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go

--加上缩进效果
select ClassID=space(b.level*4)+cast(a.ClassID as varchar),a.ClassParentID
from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID

go
--删除测试环境
drop table 表
drop function f_child

/*--测试结果
ClassID ClassParentID
----------- -------------
1 0
2 1
3 2
4 3
5 3

（所影响的行数为 5 行）

ClassID ClassParentID
-------------- ----------------
10
2 1
3 2
4 3
5 3

（所影响的行数为 5 行）
--*/

如何用一段SQL语句递归找到一个目录下的所有子目录

SQL 函数递归

给你一个例子，跟这个差不多：
CREATE TABLE T(ID INT ,PID INT)
INSERT INTO T SELECT 1,0
UNION ALL SELECT 2,0
UNION ALL SELECT 3,1
UNION ALL SELECT 4,1
UNION ALL SELECT 5,2
UNION ALL SELECT 6,4
UNION ALL SELECT 7,3
UNION ALL SELECT 8,4
GO
--测试数据

CREATE FUNCTION F(@ID INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @R VARCHAR(20)
SET @R=@ID
WHILE ISNULL(@ID,'')<>''
BEGIN
SELECT @R=CAST(PID AS VARCHAR(20))+'.'+@R FROM T WHERE ID=@ID
SELECT @ID=PID FROM T WHERE ID=@ID
END
RETURN @R
END
GO
--生成目录

CREATE FUNCTION F2(@F VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE @R VARCHAR(20)
SET @R=''
SET @F=SUBSTRING(@F,4,LEN(@F))
WHILE CHARINDEX('.',@F,1)<>0
BEGIN
SET @F=SUBSTRING(@F,CHARINDEX('.',@F,1)+1,LEN(@F))
SET @R='-'+@R
END
RETURN @R
END
GO
--生成前缀符号

SELECT DBO.F2(DBO.F(ID))+CAST(ID AS VARCHAR) FROM T ORDER BY DBO.F(ID)
GO

--结果

DROP TABLE T
DROP FUNCTION F
DROP FUNCTION F2

----------------------------------------------------以下为结果集--------------------------------------------------------------

1
-3
--7
-4
--6
--8
2
-5

SQL 函数递归

SQL问题实现递归查询

下面是我做过的题目.你是哪个表,在换一下名称 create proc p_recursion @v_uid varchar(5)

begin

--创建一个临时表用于存储结果

create table #TBuidres(ID varchar(5), UID varchar(5))

--插入初始条件

insert #TBuidres(ID, UID)

select ID, UID from TBuid where ID = @v_uid

declare @v_temp varchar(5)

set @v_temp = isnull(@v_temp,'')

--为临时表打开一个游标

declare vp_i cursor for select ID from #TBuidres

open vp_i

fetch next from vp_i into @v_temp

--根据临时表中ID去寻找TBuid中对应UID的记录插入临时表

while @@FETCH_STATUS = 0

begin

insert #TBuidres(ID,UID)

select ID,UID from TBuid where UID = @v_temp

fetch next from vp_i into @v_temp

end

--返回结果

select ID,UID from #TBuidres

--处理游标后事

close vp_i

deallocate vp_i

end

--存储过程到此结束---------------------------------------------------------------------------------

--运行

exec p_recursion '001'

显示全文

全部栏目

SQL语句-递归