a b
A01 A02
B01 B02
B02 B03
B03 B04
A02 A03
C01 C02
如果查出 :
a b
A01 A03
B01 B04
C01 C02 实际业务中不是数据01,,02,03,04这些,所有只有通过以下方法推算出,用SQL怎么写
通过 A01 找到 A02 , 通过A02找到 A03,通过A03找到 A04
通过 B01 找到 B02 , 通过B02找到 B03
A01 A02
B01 B02
B02 B03
B03 B04
A02 A03
C01 C02
如果查出 :
a b
A01 A03
B01 B04
C01 C02 实际业务中不是数据01,,02,03,04这些,所有只有通过以下方法推算出,用SQL怎么写
通过 A01 找到 A02 , 通过A02找到 A03,通过A03找到 A04
通过 B01 找到 B02 , 通过B02找到 B03
insert into tb select 'A01','A02'
insert into tb select 'B01','B02'
insert into tb select 'B02','B03'
insert into tb select 'B03','B04'
insert into tb select 'A02','A03'
insert into tb select 'C01','C02'
go
;with cte as(
select a,b,1 as flg from tb a where not exists(select 1 from tb where b=a.a)
union all
select b.a,a.b,b.flg+1 from tb a inner join cte b on a.a=b.b
)select a,b from cte a where not exists(select 1 from cte where a=a.a and flg>a.flg)
/*
a b
---------- ----------
C01 C02
B01 B04
A01 A03(3 行受影响)*/
go
drop table tb
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
declare @table table (a varchar(3),b varchar(3))
insert into @table
select 'A01','A02' union all
select 'B01','B02' union all
select 'B02','B03' union all
select 'B03','B04' union all
select 'A02','A03' union all
select 'C01','C02'select a.a,COALESCE(c.b,b.b,a.b) as b from @table a
left join @table b on a.b=b.a
left join @table c on b.b=c.a
where right(a.a,1)='1'
/*
a b
---- ----
A01 A03
B01 B04
C01 C02
*/