问题没解决再发一帖
SQL 中根据 字符串 DN50|1|koujing,10|2|yalidengji
拼接出条件 where koujing='DN50' and yalidengji=10
Att 表结构如下
ID koujing yalidengji
1 DN50 10
2 DN100 20
3 DN50 15
4 DN100 30
结果如下
ID koujing yalidengji
1 DN50 10
------解决思路----------------------
declare @str varchar(1024);
set @str='DN50
------解决思路----------------------
1
------解决思路----------------------
koujing,10
------解决思路----------------------
2
------解决思路----------------------
yalidengji,DN50
------解决思路----------------------
1
------解决思路----------------------
koujing,DN50
------解决思路----------------------
1
------解决思路----------------------
koujing,10
------解决思路----------------------
2
------解决思路----------------------
yalidengji';
set @str=REPLACE(@str,',' ,' and ')
set @str=REPLACE(@str,'
------解决思路----------------------
1
------解决思路----------------------
' ,''' = ');
set @str=REPLACE(@str,'
------解决思路----------------------
2
------解决思路----------------------
' ,''' = ');
set @str='select * from Att where 1=1 and '+@str;
set @str=REPLACE(@str,'and ' ,'and ''');
print(@str);
select * from Att where 1=1 and 'DN50' = koujing and '10' = yalidengji and 'DN50' = koujing and 'DN50' = koujing and '10' = yalidengji
------解决思路----------------------
declare @s varchar(1000)='DN50
------解决思路----------------------
1
------解决思路----------------------
koujing,10
------解决思路----------------------
2
------解决思路----------------------
yalidengji,2000
------解决思路----------------------
2
------解决思路----------------------
xxxxxxxx,fsasadfasdf
------解决思路----------------------
1
------解决思路----------------------
yyyyyy';
declare @where varchar(2000)='';
with t as (select @s+',' s)
,cte as (
select *
,1 lvl
,substring(s,1,CHARINDEX(',',s)-1) one
,substring(s,CHARINDEX(',',s)+1,4000) rest
from t
union all
select t.*
,cte.lvl+1
,substring(cte.rest,1,CHARINDEX(',',cte.rest)-1)
,substring(cte.rest,CHARINDEX(',',cte.rest)+1,4000)
from t cross join cte
where CHARINDEX(',',cte.rest)>0
)
,tt as (select cte.s,lvl,cte.one,cte.one+'
------解决思路----------------------
' ss from cte)
,cte2 as (
select *
,1 lvl2
,substring(ss,1,CHARINDEX('
------解决思路----------------------
',ss)-1) one2
,substring(ss,CHARINDEX('
------解决思路----------------------
',ss)+1,4000) rest2
from tt
union all
select tt.*
,cte2.lvl2+1
,substring(cte2.rest2,1,CHARINDEX('
------解决思路----------------------
',cte2.rest2)-1)
,substring(cte2.rest2,CHARINDEX('
------解决思路----------------------
',cte2.rest2)+1,4000)
from tt join cte2 on tt.one=cte2.one
where CHARINDEX('
------解决思路----------------------
',cte2.rest2)>0
)
,ttt (id,col,type,val)as
(
select
cte2.lvl
,max(case lvl2 when 3 then one2 end)
,max(case lvl2 when 2 then one2 end)
,max(case lvl2 when 1 then one2 end)
from cte2
group by cte2.lvl
)
select @where=@where+' and '+ttt.col+'='+case when ttt.type=1 then '''' else '' end+''+ttt.val+''+case when ttt.type=1 then '''' else '' end+''
from ttt
order by id;
if len(@where)>0
set @where=' where'+SUBSTRING(@where,5,4000)
print @where;
------解决思路----------------------
-- 这边也给你回复一下
create table test (id int , koujing varchar(10) , yalidengji int)
go
insert into test (id , koujing,yalidengji) values
(1,'DN50' ,10),
(2,'DN100',20),
(3,'DN50' ,15),
(4,'DN100',30)
go
declare @condition varchar(100) = 'DN50
------解决思路----------------------
1
------解决思路----------------------
koujing,10
------解决思路----------------------
2
------解决思路----------------------
yalidengji'
declare @sql varchar(200) = 'select * from test where 1 = 1 '
declare @splitcount int , @loop int = 1
set @condition = ' ,' + @condition
set @splitcount = len(@condition) - len(replace(@condition,',',''))
while @loop <= @splitcount
begin
set @condition = replace(@condition,',',' and ''')
set @condition = REPLACE(@condition,'
------解决思路----------------------
' + cast(@loop as varchar(3)) + '
------解决思路----------------------
',''' = ')
set @loop = @loop + 1
end
set @sql = @sql + @condition
print @sql
exec (@sql )
go
drop table test
go
(4 行受影响)
select * from test where 1 = 1 and 'DN50' = koujing and '10' = yalidengji
id koujing yalidengji
----------- ---------- -----------
1 DN50 10
(1 行受影响)