表结构:Code supCode
A B
A C
A D
B B1
B B2
C D
C B
D B1
B1 B2
B1 B3
SQL 语句 找出向上 与 B3 有关系的所有的 Code 这里应该是(B1,D,C,B,A)
类似于 树形结构 从一个叶子出发 找到根(与这个叶子节点有关的所有的上级节点都要遍历到)
A B
A C
A D
B B1
B B2
C D
C B
D B1
B1 B2
B1 B3
SQL 语句 找出向上 与 B3 有关系的所有的 Code 这里应该是(B1,D,C,B,A)
类似于 树形结构 从一个叶子出发 找到根(与这个叶子节点有关的所有的上级节点都要遍历到)
找找类似的
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx
if object_id('tb') is not null drop table tb
go
create table tb (Code varchar(2),supCode varchar(2))
insert into tb
select 'A','B' union all
select 'A','C' union all
select 'A','D' union all
select 'B','B1' union all
select 'B','B2' union all
select 'C','D' union all
select 'C','B' union all
select 'D','B1' union all
select 'B1','B2' union all
select 'B1','B3';with cte as(
select code,supcode,1 as L from tb where supcode='B3'
union all
select a.code,a.supcode,b.L+1 from tb a,cte b where a.supcode=b.code
)select distinct code from cte code
----
A
B
B1
C
D(5 行受影响)
()例子:
http://tech.e800.com.cn/articles/2010/16/1262758981700_1.html
if object_id('tb') is not null
drop table tb
go
create table tb(Code varchar(4), supCode varchar(4))
go
insert into tb
select 'A', 'B'
union all select 'A', 'C'
union all select 'A', 'D'
union all select 'B', 'B1'
union all select 'B', 'B2'
union all select 'C', 'D'
union all select 'C', 'B'
union all select 'D', 'B1'
union all select 'B1', 'B2'
union all select 'B1', 'B3'
DECLARE @var varchar(4)
SET @var = 'B3'
;with cte as
(
select * from tb where supCode = @var
union all select a.* from tb a inner join cte b on a.supCode = b.Code
)select distinct Code from cte
/*
Code
----
A
B
B1
C
D(5 row(s) affected)
*/联机文档有
select distinct Code from cte OPTION (MAXRECURSION 0);
select * from tb where Code not in
(
with cte as
(
select * from tb where supCode = @var
union all select a.* from tb a inner join cte b on a.supCode = b.Code
)select distinct Code from cte
上面写的 执行报错,那应该如何修改?
)
select code,supcode,1 as L from tb where supcode='B3'
union all
select a.code,a.supcode,b.L+1 from tb a,cte b where a.supcode=b.code
)select * from tb where Code not in (
select distinct Code from cte)
with CodeList(Code)
AS
(
select Code from CodeTable where supCode='B3'
union all
select c.Code from CodeTable c inner join CodeList co on c.supCode=co.Code
)
select distinct(Code) from CodeList