字段:
ID int , TypeID , nvarchar(64)
数据:
1 1,2,3,4,5
2 1,5
3 3,7
4 2,6
5 4,5
......
要做的功能是搜索时使用的,
传进来的@TypeID='2,4' 时,符合数据的记录是,1和4和5三条记录
,不知道说的清楚不,这个查询语句怎么写啊
ID int , TypeID , nvarchar(64)
数据:
1 1,2,3,4,5
2 1,5
3 3,7
4 2,6
5 4,5
......
要做的功能是搜索时使用的,
传进来的@TypeID='2,4' 时,符合数据的记录是,1和4和5三条记录
,不知道说的清楚不,这个查询语句怎么写啊
如果仅仅是两个.declare @typeid as varchar(10)
set @typeid = '2,4'select * from tb where charindex(','+left(@type,charindex(',',@typeid)-1)+',',','+typeid+',')>0 or charindex(','+substring(@type,charindex(',',@typeid)+1,len(@typeid))+',',','+typeid+',')>0
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '3,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')
godeclare @typeid as varchar(10)
set @typeid = '2,4'select * from tb where charindex(','+left(@typeid,charindex(',',@typeid)-1)+',',','+typeid+',')>0 or charindex(','+substring(@typeid,charindex(',',@typeid)+1,len(@typeid))+',',','+typeid+',')>0drop table tb/*
ID TypeID
----------- ----------------------------------------------------------------
1 1,2,3,4,5
4 2,6
5 4,5(所影响的行数为 3 行)
*/
insert into tb select 1,'1,2,3,4,5'
insert into tb select 2,'1,5'
insert into tb select 3,'3,7'
insert into tb select 4,'2,6'
insert into tb select 5,'4,5'declare @typeid varchar(200)
set @typeid='2,4'
set @typeid='select '+replace(@typeid,',',' as id union all select ')
exec( 'select distinct a.* from tb a,('+@typeid+')b where charindex(ltrim(b.id),a.typeid)>0')id typeid
1 1,2,3,4,5
4 2,6
5 4,5
/*
功能:实现split功能的函数
*/create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
gocreate table tb (ID int , TypeID varchar(64))
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '3,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')
go--调用
declare @typeid as varchar(10)
set @typeid = '2,4'select distinct m.* from tb m,
(select * from dbo.fn_split(@typeid,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0
drop table tb
drop function dbo.fn_split /*
ID TypeID
----------- ----------------------------------------------------------------
1 1,2,3,4,5
4 2,6
5 4,5(所影响的行数为 3 行)
*/
create table tb (ID int , TypeID varchar(64))
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '23,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')
godeclare @typeid varchar(200)
declare @sql as varchar(1000)
set @typeid = '2,4'
set @sql = 'select ''' + replace(@typeid , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)drop table tb/*
ID TypeID
----------- ----------------------------------------------------------------
1 1,2,3,4,5
4 2,6
5 4,5(所影响的行数为 3 行)
*/
@s1 varchar(8000), --要比较的第一个字符串
@s2 varchar(8000), --要比较的第二个字符串
@split varchar(10) --数据分隔符
)RETURNS bit
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s1)>0
BEGIN
IF CHARINDEX(@split+LEFT(@s1,CHARINDEX(@split,@s1)-1)+@split,@split+@s2+@split)>0
RETURN(1)
SET @s1=STUFF(@s1,1,CHARINDEX(@split,@s1)+@splitlen,'')
END
RETURN(CASE WHEN CHARINDEX(@split+@s1+@split,@split+@s2+@split)>0 THEN 1 ELSE 0 END)
END
create table tb (ID int , TypeID varchar(64))
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '3,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')
go--调用
declare @typeid as varchar(10)
set @typeid = '2,4'
select ID,dbo.f_CompareSTR(TypeID,@typeid,',')
from tbdrop table tb
select t.* from
(
select distinct m.* from [View] m,
(select * from dbo.fn_split(@typeid,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0
) t
inner join ViewImg on ViewImg.ID = t.ID
or
select distinct m.* from
[View] m,
(select * from dbo.fn_split(@typeid,',')) n ,
[viewimg] o
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0 and m.id = o.id
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '3,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')declare @str varchar(10)
set @str='2,4'declare @sql varchar(100)
set @sql ='select * from tb where typeid like ''%['+replace(@str,',','')+']%'''
print @sql
exec(@sql)
@s1 varchar(8000), --要比较的第一个字符串
@s2 varchar(8000), --要比较的第二个字符串
@split varchar(10) --数据分隔符
)RETURNS bit
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s1)> 0
BEGIN
IF CHARINDEX(@split+LEFT(@s1,CHARINDEX(@split,@s1)-1)+@split,@split+@s2+@split)> 0
RETURN(1)
SET @s1=STUFF(@s1,1,CHARINDEX(@split,@s1)+@splitlen,'')
END
RETURN(CASE WHEN CHARINDEX(@split+@s1+@split,@split+@s2+@split)> 0 THEN 1 ELSE 0 END)
END drop table tb
create table tb (ID int , TypeID varchar(64))
insert into tb values(1, '1,2,3,4,5')
insert into tb values(2, '1,5')
insert into tb values(3, '3,7')
insert into tb values(4, '2,6')
insert into tb values(5, '4,5')
go --调用
declare @typeid as varchar(10)
set @typeid = '2,4'
select ID,TypeID
from tb where dbo.f_CompareSTR(TypeID,@typeid,',') <>0
-----------------------------------这样不行吗?
2 in (1,2,3,4,5)
2 in (12,22,32,42,52)
2 in (123,245,321)这样你应该明白了吧.所以要用','+变量+','
',2,' in ',1,2,3,4,5,'
',2,' not in ',12,22,32,42,52,'
',2,' not in ',123,245,321,'