给你我个人看来比较好的数据选取方法,选取出来的数据只要再把给入的参数replace成空字符就行。declare @a nvarchar(100) = '8,92' declare @b nvarchar(100) = '3,8,83,92,215,7' ;with tb(id, ids) as ( select 1, @b ) select id, ids from tb where ids like ('%' + REPLACE(@a,',', '%') + '%')
drop table t gocreate table t(id int, ids varchar(100))insert into t select 1 , '3,8,83,92,215,7' godeclare @a varchar(100) = '8,7';with tt as ( select id, ids,@a+',' as a,ids+',' as ids_t from t where ids like ('%' + REPLACE(@a,',', '%') + '%') ),ttt as ( select id,ids, cast(a as varchar(max)) as a, cast(ids_t as varchar(max)) as ids_t , 1 as level from ttunion allselect id,ids, cast(stuff(a,1,charindex(',',a),'') as varchar(max)) , cast(replace(ids_t,left(a,charindex(',',a)),'') as varchar(max)), level + 1 from ttt where charindex(',',a) > 0) select id, ids_t from ( select id, ids,left(ids_t,len(ids_t)-1) as ids_t, ROW_NUMBER() over(partition by id order by level desc) as rownum from ttt )a where rownum = 1 /* id ids_t 1 3,83,92,215 */
唉呀,我真该死。我把问题给描述错了。应该是。 表结构 id ids 1 3,8,83,92,215,7传入一个值8,105,7要取出105 即是传入的值不存在于数据库中就取出来 非常感谢上面的前辈,同时也非常抱歉,都怪我自己理解错,之前的描述错了。现在这样还能帮我解答吗?
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2013-11-21 23:19:12 -- Version: -- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) -- Sep 22 2011 00:28:06 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[ids] varchar(15)) insert [tb] select 1,'3,8,83,92,215,7' --------------开始查询-------------------------- Select a.id,ids=substring(a.ids,b.number,charindex(',',a.ids+',',b.number)-b.number) into #tb from Tb a join master..spt_values b ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.ids) where substring(','+a.ids,b.number,1)=','
declare @str varchar(8000) set @str = '8,105,7'set @str = 'select ids='''+replace(@str,',',''''+' union all select ''')+'''' set @str='select ids into #temp from ('+@str+') a select * from #temp a where not exists(select 1 from #tb where ids=a.ids) drop table #tb,#temp'exec(@str) ----------------结果---------------------------- /* ids ---- 105(1 行受影响)*/
这样: drop table t gocreate table t(id int, ids varchar(100))insert into t select 1 , '3,8,83,92,215,7' godeclare @a varchar(100) = '8,105,7' ;with tt as ( select ids, SUBSTRING(t.ids, number ,CHARINDEX(',',t.ids+',',number)-number) as v from (select @a as ids)t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.ids,s.number,1) = ',' )select tt.ids,tt.v from tt where not exists(select 1 from t where CHARINDEX(','+tt.v+',',','+t.ids+',') > 0) /* ids v 8,105,7 105 */
DECLARE @P1 varchar(100),@p2 varchar(100),@p3 varchar(200),@p4 int,@p5 varchar(100),@p6 varchar(100)set @p1='8,105,7,99,88'+',' set @p6=@p1 set @p3='3,8,83,92,215,7'+','set @p5='' while @P1<>'' begin select @p2=substring(@p1,1,CHARINDEX(',',@p1)),@p4=CHARINDEX(',',@p1),@p1=substring(@p1,CHARINDEX(',',@p1)+1,len(@p1)) --select @p2,'提起字符串',@p4,@p1 --select @p3,@p2,(case when CHARINDEX(@p2,@p3)=0 then '不存在' else '存在' end) if CHARINDEX(@p2,@p3)=0 begin set @p5=@p5+@p2 end
declare @b nvarchar(100) = '3,8,83,92,215,7'
;with tb(id, ids) as
(
select 1, @b
)
select id, ids from tb
where ids like ('%' + REPLACE(@a,',', '%') + '%')
gocreate table t(id int, ids varchar(100))insert into t
select 1 , '3,8,83,92,215,7'
godeclare @a varchar(100) = '8,7';with tt
as
(
select id, ids,@a+',' as a,ids+',' as ids_t
from t
where ids like ('%' + REPLACE(@a,',', '%') + '%')
),ttt
as
(
select id,ids,
cast(a as varchar(max)) as a,
cast(ids_t as varchar(max)) as ids_t ,
1 as level
from ttunion allselect id,ids,
cast(stuff(a,1,charindex(',',a),'') as varchar(max)) ,
cast(replace(ids_t,left(a,charindex(',',a)),'') as varchar(max)),
level + 1
from ttt
where charindex(',',a) > 0)
select id, ids_t
from
(
select id, ids,left(ids_t,len(ids_t)-1) as ids_t,
ROW_NUMBER() over(partition by id order by level desc) as rownum
from ttt
)a
where rownum = 1
/*
id ids_t
1 3,83,92,215
*/
表结构
id ids
1 3,8,83,92,215,7传入一个值8,105,7要取出105
即是传入的值不存在于数据库中就取出来
非常感谢上面的前辈,同时也非常抱歉,都怪我自己理解错,之前的描述错了。现在这样还能帮我解答吗?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-21 23:19:12
-- Version:
-- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)
-- Sep 22 2011 00:28:06
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[ids] varchar(15))
insert [tb]
select 1,'3,8,83,92,215,7'
--------------开始查询--------------------------
Select
a.id,ids=substring(a.ids,b.number,charindex(',',a.ids+',',b.number)-b.number)
into #tb
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.ids)
where
substring(','+a.ids,b.number,1)=','
declare @str varchar(8000) set @str = '8,105,7'set @str = 'select ids='''+replace(@str,',',''''+' union all select ''')+''''
set @str='select ids into #temp from ('+@str+') a select * from #temp a where not exists(select 1 from #tb where ids=a.ids) drop table #tb,#temp'exec(@str)
----------------结果----------------------------
/* ids
----
105(1 行受影响)*/
id ids
1 3
1 8
1 83
1 92
1 215
1 7
可是我的数据源就是一个字符串'3,8,83,92,215,7',因为这个串是非常长的,近8000个字符,能否直接查询这个串,不对它做象您上面那样处理。这里因为存取比较频繁的。
这样:
drop table t
gocreate table t(id int, ids varchar(100))insert into t
select 1 , '3,8,83,92,215,7'
godeclare @a varchar(100) = '8,105,7'
;with tt
as
(
select
ids,
SUBSTRING(t.ids, number ,CHARINDEX(',',t.ids+',',number)-number) as v
from (select @a as ids)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.ids,s.number,1) = ','
)select tt.ids,tt.v
from tt
where not exists(select 1 from t
where CHARINDEX(','+tt.v+',',','+t.ids+',') > 0)
/*
ids v
8,105,7 105
*/
DECLARE @P1 varchar(100),@p2 varchar(100),@p3 varchar(200),@p4 int,@p5 varchar(100),@p6 varchar(100)set @p1='8,105,7,99,88'+','
set @p6=@p1
set @p3='3,8,83,92,215,7'+','set @p5=''
while @P1<>''
begin
select @p2=substring(@p1,1,CHARINDEX(',',@p1)),@p4=CHARINDEX(',',@p1),@p1=substring(@p1,CHARINDEX(',',@p1)+1,len(@p1))
--select @p2,'提起字符串',@p4,@p1 --select @p3,@p2,(case when CHARINDEX(@p2,@p3)=0 then '不存在' else '存在' end)
if CHARINDEX(@p2,@p3)=0
begin
set @p5=@p5+@p2
end
endselect @p3 as 数据库字符串,@p6 as 传入字符串,@p5 as 不存在的