日期:2014-05-18  浏览次数:20591 次

拜请高手解决 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=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 来解决

SQL code
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)
)


但是提示多个引用

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


------解决方案--------------------
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 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