试着写了一个,你自己看看吧create table ta ( [01] int, [02] int, [03] int, [04] int, [05] int ) declare @a varchar(100)set @a='0112,0423,0534' set @a=@a+',' declare @s1 varchar(8000) declare @s2 varchar(8000) set @s1='' set @s2='' while (select charindex(',',@a))>0 begin set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],' set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+',' set @a=right(@a,len(@a)-charindex(',',@a)) end set @s1=left(@s1,len(@s1)-1) set @s2=left(@s2,len(@s2)-1)exec ('insert into ta ('+@s1+') select '+@s2)select * from tadrop table ta/* 结果 01 02 03 04 05 ----------- ----------- ----------- ----------- ----------- 12 NULL NULL 23 34*/
按照楼上的我写了 现在想加个条件更新 该怎么写呢 我想更新@s1这个列为@s2 当mobile=‘13800000001’ 该怎么写? exec (update tbl_xiaoshoutongji set ('+@s1+') = '(+@s2)' where mobile='13800000001' ) 这样写不对啊 正确的该怎么写呢
exec里要写字符串,字段名是加[],而不是加() -_-试试看下面的exec('update tbl_xiaoshoutongji set ['+@s1+'] = '+@s2+' where mobile='+'''13800000001''' )
夜游神 use lqjt --delete tbl_xiaoshoutongji declare @a varchar(100) , @b varchar(50)set @a='ac12,ab43,ad123' set @b='13500000001' set @a=@a+',' declare @s1 varchar(8000) declare @s2 varchar(8000) set @s1='' set @s2='' while (select charindex(',',@a))>0 begin set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],' set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+',' set @a=right(@a,len(@a)-charindex(',',@a)) end set @s1=left(@s1,len(@s1)-1) set @s2=left(@s2,len(@s2)-1) exec('update tbl_xiaoshoutongji set ['+@s1+'] = '+@s2+' where mobile='+'''13500000001''' )能编译但是不能运行啊? 服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: ',' 附近有语法错误。 服务器: 消息 105,级别 15,状态 1,行 1 字符串 'ad] = 12,43,123 where mobile='13500000001'' 之前有未闭合的引号。
忘了-_-用这个 exec ('update tbl_xiaoshoutongji set ('+@s1+') = '+@s2+' where mobile='+'''13500000001''' )
又错了-_-,你等下,Update多列不是这样写的,上面这个只适合Update一列
把语句print出来就可以看出来了 update 多列update 表 set col=值,col2=值 from ... where ..
更新的话,while以下改写成这样 while (select charindex(',',@a))>0 begin set @s1=@s2+'['+left(left(@a,charindex(',',@a)-1),2)+']=' set @s2=@s1+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+',' set @a=right(@a,len(@a)-charindex(',',@a)) end set @s2=left(@s2,len(@s2)-1) select ('update tbl_xiaoshoutongji set '+@s2+' where mobile='+'''13500000001''' )
搞定了 这样就可以了 use lqjt --delete tbl_xiaoshoutongji declare @a varchar(100) , @b varchar(50)set @a='ac12,ab43,ad123' set @b='13500000001' set @a=@a+',' declare @s1 varchar(8000) declare @s2 varchar(8000) set @s1='mobile,' set @s2=@b+',' while (select charindex(',',@a))>0 begin set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],' set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+',' set @a=right(@a,len(@a)-charindex(',',@a)) end set @s1=left(@s1,len(@s1)-1) set @s2=left(@s2,len(@s2)-1) exec ('insert into tbl_xiaoshoutongji ('+@s1+') select '+@s2 ) 大家方便加我QQ吗? 夜游神 谢谢你了
以上结果如下 /* update tbl_xiaoshoutongji set [01]=12,[04]=23,[05]=34 where mobile='13500000001' */需要执行的话,把select改为exec
(
[01] int,
[02] int,
[03] int,
[04] int,
[05] int
)
declare @a varchar(100)set @a='0112,0423,0534'
set @a=@a+','
declare @s1 varchar(8000)
declare @s2 varchar(8000)
set @s1=''
set @s2=''
while (select charindex(',',@a))>0
begin
set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],'
set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+','
set @a=right(@a,len(@a)-charindex(',',@a))
end
set @s1=left(@s1,len(@s1)-1)
set @s2=left(@s2,len(@s2)-1)exec ('insert into ta ('+@s1+') select '+@s2)select * from tadrop table ta/*
结果
01 02 03 04 05
----------- ----------- ----------- ----------- -----------
12 NULL NULL 23 34*/
该怎么写? exec (update tbl_xiaoshoutongji set ('+@s1+') = '(+@s2)' where mobile='13800000001' ) 这样写不对啊 正确的该怎么写呢
--delete tbl_xiaoshoutongji
declare @a varchar(100) , @b varchar(50)set @a='ac12,ab43,ad123'
set @b='13500000001'
set @a=@a+','
declare @s1 varchar(8000)
declare @s2 varchar(8000)
set @s1=''
set @s2=''
while (select charindex(',',@a))>0
begin
set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],'
set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+','
set @a=right(@a,len(@a)-charindex(',',@a))
end
set @s1=left(@s1,len(@s1)-1)
set @s2=left(@s2,len(@s2)-1)
exec('update tbl_xiaoshoutongji set ['+@s1+'] = '+@s2+' where mobile='+'''13500000001''' )能编译但是不能运行啊?
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
服务器: 消息 105,级别 15,状态 1,行 1
字符串 'ad] = 12,43,123 where mobile='13500000001'' 之前有未闭合的引号。
exec ('update tbl_xiaoshoutongji set ('+@s1+') = '+@s2+' where mobile='+'''13500000001''' )
while (select charindex(',',@a))>0
begin
set @s1=@s2+'['+left(left(@a,charindex(',',@a)-1),2)+']='
set @s2=@s1+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+','
set @a=right(@a,len(@a)-charindex(',',@a))
end
set @s2=left(@s2,len(@s2)-1)
select ('update tbl_xiaoshoutongji set '+@s2+' where mobile='+'''13500000001''' )
--------------------------------------------------------------------------------
看是可以看到,问题是他要求太BT,要做交叉循环……(用更新完了的@s1来更新@s2)-_-
--delete tbl_xiaoshoutongji
declare @a varchar(100) , @b varchar(50)set @a='ac12,ab43,ad123'
set @b='13500000001'
set @a=@a+','
declare @s1 varchar(8000)
declare @s2 varchar(8000)
set @s1='mobile,'
set @s2=@b+','
while (select charindex(',',@a))>0
begin
set @s1=@s1+'['+left(left(@a,charindex(',',@a)-1),2)+'],'
set @s2=@s2+substring((left(@a,charindex(',',@a)-1)),3,len(left(@a,charindex(',',@a)-1))-1)+','
set @a=right(@a,len(@a)-charindex(',',@a))
end
set @s1=left(@s1,len(@s1)-1)
set @s2=left(@s2,len(@s2)-1)
exec ('insert into tbl_xiaoshoutongji ('+@s1+') select '+@s2 )
大家方便加我QQ吗? 夜游神 谢谢你了
/*
update tbl_xiaoshoutongji set [01]=12,[04]=23,[05]=34 where mobile='13500000001'
*/需要执行的话,把select改为exec