查询BOM死循环

 

 

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