--改调用方式就行了:select a.*
from t_book a
where exists(select 1 from f_cla_id(0) where ','+a.book_classes+',' like cla_sid)
from t_book a
where exists(select 1 from f_cla_id(0) where ','+a.book_classes+',' like cla_sid)
create table t_class(cla_id int,cla_name varchar(10),cla_parent_id int)
insert t_class select 1,'考研', 0
union all select 2,'计算机',0
union all select 3,'英语', 1
union all select 4,'数学', 1create table t_book(book_id int,book_name varchar(10),book_classes varchar(10))
insert t_book select 1,'*******','1,1,1'
union all select 2,'*******','2,2,3'
union all select 3,'*******','3,3,3'
go--得到指定cla_id的所有子
create function f_cla_id(@cla_id int)
returns @re table(cla_id int,cla_sid varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select @cla_id,'%,'+cast(@cla_id as varchar)+',%',@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.cla_id,'%,'+cast(a.cla_id as varchar)+',%',@l
from t_class a join @re b on a.cla_parent_id=b.cla_id
where b.level=@l-1
end
return
end
go--调用函数实现楼主的查询
select a.*
from t_book a
where exists(select 1 from f_cla_id(0) where ','+a.book_classes+',' like cla_sid)
go--删除测试
drop table t_class,t_book
drop function f_cla_id/*--测试结果book_id book_name book_classes
----------- ---------- ------------
1 ******* 1,1,1
2 ******* 2,2,3
3 ******* 3,3,3(所影响的行数为 3 行)
--*/
returns bit
as
begin
declare @t table(cla_id int,level int)
declare @l int,@re bit
select @l=0,@re=1,@book_classes=','+@book_classes+','
if @book_classes not like '%,'+cast(@cla_id as varchar)+',%'
insert @t select @cla_id,@l
while @@rowcount>0
begin
set @l=@l+1
if exists(select 1
from t_class a join @t b on a.cla_parent_id=b.cla_id
where b.level=@l-1
and @book_classes like '%,'+cast(a.cla_id as varchar)+',%')
goto lb_re
else
insert @t select a.cla_id,@l
from t_class a join @t b on a.cla_parent_id=b.cla_id
where b.level=@l-1
end
set @re=0lb_re:
return(@re)
end
go--调用函数实现楼主的查询
select a.*
from t_book a
where dbo.f_check(0,book_classes)=1
create table t_class(cla_id int,cla_name varchar(10),cla_parent_id int)
insert t_class select 1,'考研', 0
union all select 2,'计算机',0
union all select 3,'英语', 1
union all select 4,'数学', 1create table t_book(book_id int,book_name varchar(10),book_classes varchar(10))
insert t_book select 1,'*******','1,1,1'
union all select 2,'*******','2,2,3'
union all select 3,'*******','3,3,3'
go--处理函数
create function f_check(@cla_id int,@book_classes varchar(100))
returns bit
as
begin
declare @t table(cla_id int,level int)
declare @l int,@re bit
select @l=0,@re=1,@book_classes=','+@book_classes+','
if @book_classes not like '%,'+cast(@cla_id as varchar)+',%'
insert @t select @cla_id,@l
while @@rowcount>0
begin
set @l=@l+1
if exists(select 1
from t_class a join @t b on a.cla_parent_id=b.cla_id
where b.level=@l-1
and @book_classes like '%,'+cast(a.cla_id as varchar)+',%')
goto lb_re
else
insert @t select a.cla_id,@l
from t_class a join @t b on a.cla_parent_id=b.cla_id
where b.level=@l-1
end
set @re=0lb_re:
return(@re)
end
go--调用函数实现楼主的查询
select a.*
from t_book a
where dbo.f_check(0,book_classes)=1
go--删除测试
drop table t_class,t_book
drop function f_check/*--测试结果book_id book_name book_classes
----------- ---------- ------------
1 ******* 1,1,1
2 ******* 2,2,3
3 ******* 3,3,3(所影响的行数为 3 行)
--*/