假定表 tbList 数据如下
memA memB
1 2
2 1
3 2
2 3
4 2
2 4上面的数据表示了 1和2 是兄弟关系 3和2是兄弟 4和2是兄弟在学习WITH CTE的时候,我想通过公用表达式 WITH CTE 来查找出某个项目例如 1 的兄弟和1兄弟的兄弟(深度不限)于是出现如下代码declare @memA int set @memA=1
with cte as(
select memB from tbList where memA=@memA union all
select memB from tbList inner join cte on tbList.memA=cte.memB
)问题出现了,会出现回路的,结果就是无限的递归循环 1->2->1->2->1……
本想通过子查询 exists 来解决declare @memA int set @memA=1
with cte as(
select memB from tbList where memA=@memA union all
select tbList.memB from tbList inner join cte on tbList.memA=cte.memB
where not exists (select 1 from cte where cte.memB=tbList.memB)
)但是提示多个引用这样的话,该怎么解决掉呢,恳求各位教教我
号是朋友的,不敢多用他分,一点分辛苦各位了
memA memB
1 2
2 1
3 2
2 3
4 2
2 4上面的数据表示了 1和2 是兄弟关系 3和2是兄弟 4和2是兄弟在学习WITH CTE的时候,我想通过公用表达式 WITH CTE 来查找出某个项目例如 1 的兄弟和1兄弟的兄弟(深度不限)于是出现如下代码declare @memA int set @memA=1
with cte as(
select memB from tbList where memA=@memA union all
select memB from tbList inner join cte on tbList.memA=cte.memB
)问题出现了,会出现回路的,结果就是无限的递归循环 1->2->1->2->1……
本想通过子查询 exists 来解决declare @memA int set @memA=1
with cte as(
select memB from tbList where memA=@memA union all
select tbList.memB from tbList inner join cte on tbList.memA=cte.memB
where not exists (select 1 from cte where cte.memB=tbList.memB)
)但是提示多个引用这样的话,该怎么解决掉呢,恳求各位教教我
号是朋友的,不敢多用他分,一点分辛苦各位了
select distinct memB from
(
select memB from tbList where memA=@memA or memB=@memA
union all
select memA from tbList where memA=@memA or memB=@memA
) aa where memB <>@memA
但是代码长了点主要CTE里无法再次用子循环查找
create table [tb] (id int,name varchar(1),pid int)
insert into [tb]
select 1,'A',0 union all
select 2,'B',1 union all
select 3,'D',1 union all
select 4,'C',2 union all
select 5,'D',2 union all
select 6,'A',4 union all
select 7,'E',5 union all
select 8,'F',5
GO
;with cte
as
(
select *,[path]=cast([name]+'->' as varchar(100)) ,[level] = 1 from tb where pid = 0
union all
select a.*, cast(c.[path]+a.[name]+'->' as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.id
)
select
*
from cte
where len([path]) > 6 and right([path],3) = left([path],3)
/*
id name pid path level
----------- ---- ----------- -------------- -----
6 A 4 A->B->C->A-> 4(1 行受影响)
*/------------------------------------------------------------------------
-- Author : happyflystone
-- Date : 2010-04-06
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))
Go
INSERT INTO tb
SELECT 'A','B' UNION ALL
SELECT 'A','D' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'B','D' UNION ALL
SELECT 'C','A' UNION ALL
SELECT 'D','E' UNION ALL
SELECT 'D','F'
GO
--Start
;with cte
as
(
select *,[path]=cast([cid]+'->' as varchar(100)) ,[level] = 1
from (select distinct cid,cast('' as nvarchar(1)) as pid from tb union select distinct pid ,'' from tb) b
union all
select a.*,cast(a.[cid]+'->'+c.[path] as varchar(100)),[level]+1
from cte c ,tb a
where a.pid = c.cid and charindex(a.[cid]+'->',c.[path])=0
)
select
[path]+cid+'->'
from cte
where exists(select 2 from tb where cid+'->' = right([path],3) and pid+'->' = left([path],3))-- = left([path],3)
--Result:
/*
--------------
A->B->C->A->
C->A->B->C->
B->C->A->B->(3 行受影响)*/
--End
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/06/5456223.aspx