A表
Attribute --一个字段
1,2,3,2,5
1,2,3,4,5
3,3,3,1,5
4,5,3,4,51,2,5,4,5
1,2,5,4,5
2,3,6,5,7
2,3,6,5,7有一条件,比如说(0,0,3,4,5) --'0'表示获取所有记录,条件是动态的,那是相当的困难啊DECLARE @temp VARCHAR(500)
SET @temp='0,0,3,0,5' --条件是动态的用到了一些字符切割,可是我切不出来呀-_-!求解...我还是一新手,遇到这种情况,简直是悲剧啊...
Attribute --一个字段
1,2,3,2,5
1,2,3,4,5
3,3,3,1,5
4,5,3,4,51,2,5,4,5
1,2,5,4,5
2,3,6,5,7
2,3,6,5,7有一条件,比如说(0,0,3,4,5) --'0'表示获取所有记录,条件是动态的,那是相当的困难啊DECLARE @temp VARCHAR(500)
SET @temp='0,0,3,0,5' --条件是动态的用到了一些字符切割,可是我切不出来呀-_-!求解...我还是一新手,遇到这种情况,简直是悲剧啊...
是不是第三个数字是3第五个是5的Attribute 就行?
declare @temp varchar(500)
declare @str nvarchar(4000)
--用'0,0,3,0,5'测试
set @temp='0,0,3,0,5'
if substring(@temp,1,1)!='0'
set @str=' and substring(Attribute,1,1)=substring('''+@temp+''',1,1)'
if substring(@temp,3,1)!='0'
set @str=isnull(@str,'')+' and substring(Attribute,3,1)=substring('''+@temp+''',3,1)'
if substring(@temp,5,1)!='0'
set @str=isnull(@str,'')+' and substring(Attribute,5,1)=substring('''+@temp+''',5,1)'
if substring(@temp,7,1)!='0'
set @str=isnull(@str,'')+' and substring(Attribute,7,1)=substring('''+@temp+''',7,1)'
if substring(@temp,9,1)!='0'
set @str=isnull(@str,'')+' and substring(Attribute,9,1)=substring('''+@temp+''',9,1)'
set @str='select Attribute from A表 where 1=1 '+@str
exec(@str)
declare @a table(Attribute VARCHAR(200)) --一个字段
insert @a select '1,2,3,2,5'
union all select '1,2,3,4,5'
union all select '3,3,3,1,5'
union all select '4,5,3,4,5'
union all select '1,2,5,4,5'
union all select '1,2,5,4,5'
union all select '2,3,6,5,7'
union all select '2,3,6,5,7' DECLARE @temp VARCHAR(500)
SET @temp='0,0,3,0,5' --条件是动态的 SELECT * FROM @a WHERE ','+attribute+',' LIKE REPLACE(','+@temp+',','0','%')--result
/*Attribute
--------------------------------------------------
1,2,3,2,5
1,2,3,4,5
3,3,3,1,5
4,5,3,4,5(所影响的行数为 4 行)
*/
declare @T table(Attribute varchar(30))
insert into @T
select '1,2,3,2,5' union all
select '1,2,3,4,5' union all
select '3,3,3,1,5' union all
select '4,5,3,4,5' union all
select '1,2,5,4,5' union all
select '1,2,5,4,5' union all
select '2,3,6,5,7' union all
select '2,3,6,5,7' DECLARE @temp VARCHAR(20)
SET @temp='0,0,3,4,5' select A.* from @T A where Attribute in
(
select Attribute from
(
select Attribute,
value,
level = row_number() over(partition by Attribute order by number)
from
(
select Attribute,
value = substring(Attribute,number,charindex(',',Attribute+',',number)-number),
number
from (select distinct * from @T) a, master..spt_values b
where b.type = 'P' and substring(','+a.Attribute,number,1)=','
)T
)A,
(
SELECT * FROM
(
select value,
level = row_number() over(order by number)
from
(
select value = substring(@temp,number,charindex(',',@temp+',',number)-number),
number
from master..spt_values b
where b.type = 'P' and substring(','+@temp,number,1)=','
)T
)T
WHERE VALUE <>0
)B
where A.level = B.level and A.value = B.value
group by Attribute
having count(Attribute) = (select count(value) from (
SELECT * FROM
(
select value,
level = row_number() over(order by number)
from
(
select value = substring(@temp,number,charindex(',',@temp+',',number)-number),
number
from master..spt_values b
where b.type = 'P' and substring(','+@temp,number,1)=','
)T
)T
WHERE VALUE <>0
)B
)
)/*
1,2,3,4,5
4,5,3,4,5
*/
returns @temp table([content] varchar(100))
/*--实现split功能 的函数 */
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
DECLARE @temp VARCHAR(500)
SET @temp='0,0,3,0,5' --条件是动态的 SELECT * FROM dbo.f_split(@temp,',')content
--------
0
0
3
0
5
(5 行受影响)