参数@category varchar(50)
比如@category='1,2,3';表 blog 中有个字段CategoryIDList 是字符型的
id CategoryIDList
1 '1,9'
2 '5,7'
3 '2,3,4'
---------
我想查出
id CategoryIDList
1 '1,9'
3 '2,3,4'根据CategoryIDlist字段 逗号隔开的数字 @category中逗号隔开存在的.
说的不是很清楚.看例子应该明白我想要的吧.
比如@category='1,2,3';表 blog 中有个字段CategoryIDList 是字符型的
id CategoryIDList
1 '1,9'
2 '5,7'
3 '2,3,4'
---------
我想查出
id CategoryIDList
1 '1,9'
3 '2,3,4'根据CategoryIDlist字段 逗号隔开的数字 @category中逗号隔开存在的.
说的不是很清楚.看例子应该明白我想要的吧.
insert into tb select 1, '1,9'
insert into tb select 2, '5,7'
insert into tb select 3, '2,3,4' --查询
declare @category varchar(50)
set @category='1,2,3';
select * from tb where dbo.f_1(CategoryIDList,@category)>0
/*
1 1,9
3 2,3,4
*/--自定义函数
create function f_1(@str1 varchar(200),@str2 varchar(200))
returns int
as
begin
declare @cnt int set @cnt=0
select @cnt=count(*) from f_split(@str1,',') as a
inner join f_split(@str2,',') as b on a.a=b.a
return @cnt
end
--常用split函数:
create function f_split(@str varchar(8000),@strseprate varchar(1))
returns @temp table(id int identity(1,1),a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@strseprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为stuff,可以自己试着改写一下
set @i=charindex(@strseprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[CategoryIDList] nvarchar(9))
Insert tb
select 1,'1,9' union all
select 2,'5,7' union all
select 3,'2,3,4'
Go
Select * from tb
---生成存储过程
if object_id('p1')<>0 drop proc p1
go
create procedure p1
@category varchar(50)
as
declare @t table(col varchar(10))while charindex(',',@category)>0
begin
insert @t
select left(@category,charindex(',',@category)-1) set @category =stuff(@category ,1,charindex(',',@category),'')
end
insert into @t values(@category)select distinct CategoryIDList
from tb,@t a
where CategoryIDList like '%'+a.col+'%'--调用
exec p1 '1,2,3'结果:
1,9
2,3,4
declare @category varchar(50)
set @category='1,2,3'; declare @sql varchar(4000)
set @sql='select idno ='''+replace(@category,',','''union all select''')+''''
exec ('select * into tb from ('+@sql+ ')a')declare @tb table(id int,CategoryIDList varchar(20))
insert into @tb select 1,'1,9'
union all select 3,'2,3,4'
select distinct a.* from @tb a
left join tb b
on a.CategoryIDList like '%'+b.idno+'%' drop table tb/*
(所影响的行数为 3 行)
(所影响的行数为 2 行)id CategoryIDList
----------- --------------------
1 1,9
3 2,3,4(所影响的行数为 2 行)
*/