declare @fieldlist varchar(100),
@valuelist varchar(100),
@field1 varchar(10),
@value1 varchar(10),
@sqlstr varchar(300) select @fieldlist='aa,bb,cc,dd'
select @valuelist='11,22,33,44'
select @sqlstr = ''
while (charindex(',',@fieldlist)>0 and charindex(',',@valuelist)>0)
begin
select @field1 = substring(@fieldlist,1,charindex(',',@fieldlist)-1)
select @value1 = substring(@valuelist,1,charindex(',',@valuelist)-1)
select @sqlstr = @sqlstr + @field1+'='+@valuelist+','
select @fieldlist = substring(@fieldlist,charindex(',',@fieldlist)+1,len(@fieldlist))
select @valuelist = substring(@valuelist,charindex(',',@valuelist)+1,len(@valuelist))
end
select @sqlstr = @sqlstr + @fieldlist + '='+ @valuelist
@valuelist varchar(100),
@field1 varchar(10),
@value1 varchar(10),
@sqlstr varchar(300) select @fieldlist='aa,bb,cc,dd'
select @valuelist='11,22,33,44'
select @sqlstr = ''
while (charindex(',',@fieldlist)>0 and charindex(',',@valuelist)>0)
begin
select @field1 = substring(@fieldlist,1,charindex(',',@fieldlist)-1)
select @value1 = substring(@valuelist,1,charindex(',',@valuelist)-1)
select @sqlstr = @sqlstr + @field1+'='+@valuelist+','
select @fieldlist = substring(@fieldlist,charindex(',',@fieldlist)+1,len(@fieldlist))
select @valuelist = substring(@valuelist,charindex(',',@valuelist)+1,len(@valuelist))
end
select @sqlstr = @sqlstr + @fieldlist + '='+ @valuelist
解决方案 »
- 初学数据库。问个很基础的问题,麻烦了1
- 请教一条SQL语句
- 求 SQL 創建規則語句一條
- 讨论: 对于一个SQL语句,where后面的条件越多,那么查询速度会怎么样?
- 请教:除了使用全文索引,有什么办法可以优化 title like '%keys%' 这种查询
- 请大家帮忙啊
- sql树形查询,要求返回最高父节点信息,用用户自定义函数
- 用触发器生成消息队列
- 学好了SQL Server数据库,再学习Oracle数据库,是不是没有必要呀?(或则:学好了Oracle数据库,就没有必要再学习SQL Server数据库了)
- 怎样实现在sql server中执行一个脚本,能够从远程oracle数据库复制表
- 两个表联合我要联合二个表的一些特定数据,就像联合一个带where的查询结果??火急!!!
- sp_executesql的问题
declare @aa2 varchar(100),@bb2 int,@cc2 int
declare @result varchar(2000)set @aa1='aaaa,bb,ca,s,a'
set @aa2='11,22,33,44,45'
set @bb1=0
set @bb2=0set @cc1=charindex(',',@aa1)
set @cc2=charindex(',',@aa2)set result=substring(@aa1,@bb1,@cc1-@bb1)+'='+substring(@aa2,@bb2,@cc2-@bb2)
while @cc1>0
begin
set @bb1=@cc1+1
set @bb2=@cc2+1
set @cc1=charindex(',',@aa1,@bb1)
set @cc2=charindex(',',@aa2,@bb2)
set result=result+','+substring(@aa1,@bb1,case when @cc1>0 then @cc1-@bb1 else len(@aa1) end)+'='+substring(@aa2,@bb2,case when @cc2>0 then @cc2-@bb2 else len(@aa2) end)
endselect @result
@valuelist varchar(100),
@field1 varchar(10),
@value1 varchar(10),
@sqlstr varchar(300) select @fieldlist='aa,bb,cc,dd'
select @valuelist='11,22,33,44'
select @sqlstr = ''
while (charindex(',',@fieldlist)>0 and charindex(',',@valuelist)>0)
begin
select @field1 = substring(@fieldlist,1,charindex(',',@fieldlist)-1)
select @value1 = substring(@valuelist,1,charindex(',',@valuelist)-1)
select @sqlstr = @sqlstr + @field1+'='+@value1+','
select @fieldlist = substring(@fieldlist,charindex(',',@fieldlist)+1,len(@fieldlist))
select @valuelist = substring(@valuelist,charindex(',',@valuelist)+1,len(@valuelist))
end
select @sqlstr = @sqlstr + @fieldlist + '='+ @valuelist
declare @aa1 varchar(100),@bb1 int,@cc1 int
declare @aa2 varchar(100),@bb2 int,@cc2 int
declare @result varchar(2000)set @aa1='aaaa,bb,ca,s,a'
set @aa2='11,22,33,44,45'
set @bb1=0
set @bb2=0set @cc1=charindex(',',@aa1)
set @cc2=charindex(',',@aa2)set @result=substring(@aa1,@bb1,@cc1-@bb1)+'='+substring(@aa2,@bb2,@cc2-@bb2)
while @cc1>0
begin
set @bb1=@cc1+1
set @bb2=@cc2+1
set @cc1=charindex(',',@aa1,@bb1)
set @cc2=charindex(',',@aa2,@bb2)
set @result=result+','+substring(@aa1,@bb1,case when @cc1>0 then @cc1-@bb1 else len(@aa1) end)+'='+substring(@aa2,@bb2,case when @cc2>0 then @cc2-@bb2 else len(@aa2) end)
endselect @result
他妈的,我买的那本铁道出版社出的《SQL SEREVER 2000 彻底研究》简直就是烂书中的典型,连right函数的作用都写错了,说什么作用是:“取字符串的第int_expr+1个起到字符串的最后一个字符的子字符串。”
哈。。还是看帮助文件保险啊!
问题解决,给分
declare @aa1 varchar(100),@bb1 int,@cc1 int
declare @aa2 varchar(100),@bb2 int,@cc2 int
declare @result varchar(2000)set @aa1='aaaa,bb,ca,s,a,'
set @aa2='11,22,33,44,45,'
set @bb1=0
set @bb2=0
set @result=''while charindex(',',@aa1,@bb1)>0
begin
set @cc1=charindex(',',@aa1,@bb1)
set @cc2=charindex(',',@aa2,@bb2)
set @result=@result+substring(@aa1,@bb1,@cc1-@bb1)+'='+substring(@aa2,@bb2,@cc2-@bb2)+','
set @bb1=@cc1+1
set @bb2=@cc2+1
end
set @result=left(@result,len(@result)-1)
print @result