BOM查询

[mnbv134533]

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

[-晴天]

create table tb(a varchar(10),b varchar(10))
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

[--小F--]

BOM循环?

[--小F--]

参考:
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
*/

[mnbv134533]

[Quote=引用 2 楼 fredrickhu 的回复:]
BOM循环?
[/Quote]
是的 BOM循环

[mnbv134533]

[Quote=引用 3 楼 fredrickhu 的回复:]
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',……
[/Quote]
这个要 sql server 2005的才能实现是吧