呵呵,也行 先把你以前写的贴出来--测试--测试数据 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 join f_cla_id(1) b on ','+a.book_classes+',' like b.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如果我要想查询select a.* from t_book a join f_cla_id(3) b on ','+a.book_classes+',' like b.cla_sid应该可以选出2本书来 classes为'2, 2, 3'为'3, 3, 3'的都应该能选出来,可是按你的写法,只能查出'3, 3, 3'的来 我想是这样的,classes三个id中只要有一个在f_cla_id中就可以了,你写的好像是得全在才可以 谢谢~~
--测试--测试数据 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 join f_cla_id(3) b on ','+a.book_classes+',' like b.cla_sid go--删除测试 drop table t_class,t_book drop function f_cla_id/*--测试结果 book_id book_name book_classes ----------- ---------- ------------ 2 ******* 2,2,3 3 ******* 3,3,3(所影响的行数为 2 行) --*/
先把你以前写的贴出来--测试--测试数据
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 join f_cla_id(1) b
on ','+a.book_classes+',' like b.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如果我要想查询select a.*
from t_book a join f_cla_id(3) b
on ','+a.book_classes+',' like b.cla_sid应该可以选出2本书来
classes为'2, 2, 3'为'3, 3, 3'的都应该能选出来,可是按你的写法,只能查出'3, 3, 3'的来
我想是这样的,classes三个id中只要有一个在f_cla_id中就可以了,你写的好像是得全在才可以
谢谢~~
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 join f_cla_id(3) b
on ','+a.book_classes+',' like b.cla_sid
go--删除测试
drop table t_class,t_book
drop function f_cla_id/*--测试结果
book_id book_name book_classes
----------- ---------- ------------
2 ******* 2,2,3
3 ******* 3,3,3(所影响的行数为 2 行)
--*/
多谢高人