类别 明细类别 a b,c,d b e,f f g,h e f,a這個表中a,b,f,e是別是(b,c,d)﹑(e,f)﹑(g,h)﹑(f,a)的父類﹐是不是﹖
--这个问题写个自定义函数查询吧--示例数据 create table tb(类别 varchar(10),明细类别 varchar(100)) insert tb select 'a','b,c,d' union all select 'b','e,f' union all select 'f','g,h' union all select 'e','f,a' go--查询函数 create function f_chk( @类别 varchar(10) )returns bit as begin declare @l int,@re bit select @re=1,@l=0 if exists(select * from tb where 类别=@类别 and charindex(','+@类别+',',','+明细类别+',')>0) goto lb_re declare @r table(类别 varchar(10),level int) insert @r select a.类别,@l from tb a,tb b where charindex(','+a.类别+',',','+b.明细类别+',')>0 and b.类别=@类别 while @@rowcount>0 and @l<10 begin if exists(select * from @r where 类别=@类别 and level=@l) goto lb_re set @l=@l+1 insert @r select a.类别,@l from tb a,tb b,@r c where c.level=@l-1 and charindex(','+a.类别+',',','+b.明细类别+',')>0 and b.类别=c.类别 end set @re=0lb_re: return(@re) end go--调用函数查询形成循环的记录 select * from tb where dbo.f_chk(类别)=1 go--删除测试 drop table tb drop function f_chk/*--测试结果类别 明细类别 ---------- --------------------- a b,c,d b e,f e f,a(所影响的行数为 3 行) --*/
a b,c,d
b e,f
f g,h
e f,a這個表中a,b,f,e是別是(b,c,d)﹑(e,f)﹑(g,h)﹑(f,a)的父類﹐是不是﹖
create table tb(类别 varchar(10),明细类别 varchar(100))
insert tb select 'a','b,c,d'
union all select 'b','e,f'
union all select 'f','g,h'
union all select 'e','f,a'
go--查询函数
create function f_chk(
@类别 varchar(10)
)returns bit
as
begin
declare @l int,@re bit
select @re=1,@l=0 if exists(select * from tb where 类别=@类别 and charindex(','+@类别+',',','+明细类别+',')>0)
goto lb_re declare @r table(类别 varchar(10),level int)
insert @r select a.类别,@l
from tb a,tb b
where charindex(','+a.类别+',',','+b.明细类别+',')>0
and b.类别=@类别
while @@rowcount>0 and @l<10
begin
if exists(select * from @r where 类别=@类别 and level=@l)
goto lb_re
set @l=@l+1
insert @r select a.类别,@l
from tb a,tb b,@r c
where c.level=@l-1
and charindex(','+a.类别+',',','+b.明细类别+',')>0
and b.类别=c.类别
end
set @re=0lb_re:
return(@re)
end
go--调用函数查询形成循环的记录
select * from tb where dbo.f_chk(类别)=1
go--删除测试
drop table tb
drop function f_chk/*--测试结果类别 明细类别
---------- ---------------------
a b,c,d
b e,f
e f,a(所影响的行数为 3 行)
--*/