拜请高手解决 SQL WITH CTE,该如何处理

拜请高手解决 SQL WITH CTE
假定表 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兄弟的兄弟(深度不限)

于是出现如下代码

SQL code

declare @memA int set @memA=1with 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 来解决

SQL code

declare @memA int set @memA=1with 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))

但是提示多个引用

这样的话，该怎么解决掉呢，恳求各位教教我
号是朋友的，不敢多用他分，一点分辛苦各位了


[解决办法]

SQL code

if object_id('[tb]') is not null drop table [tb]create table [tb] (id int,name varchar(1),pid int)insert into [tb]select 1,'A',0 union allselect 2,'B',1 union allselect 3,'D',1 union allselect 4,'C',2 union allselect 5,'D',2 union allselect 6,'A',4 union allselect 7,'E',5 union allselect 8,'F',5GO;with cteas(    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 ctewhere 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: taIF OBJECT_ID('[tb]') IS NOT NULL     DROP TABLE [tb]GoCREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))GoINSERT 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 cteas(    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 ctewhere 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 

 

查看更多 下一篇