日期:2014-05-18 浏览次数:20576 次
if not object_id('t1') is null drop table t1 Go Create table t1([ID] int,[MONTH] int,[1] nvarchar(1),[2] nvarchar(1),[3] nvarchar(1),[4] nvarchar(1)) Insert t1 select 1,1,N'A',N'B',N'C',N'D' union all select 2,1,N'A',N'B',N'C',N'D' union all select 3,1,N'A',N'B',N'C',N'D' union all select 4,2,N'A',N'B',N'C',N'D' Go if not object_id('t2') is null drop table t2 Go Create table t2([ID] int,[MONTH] int,[字段值] int,[Value] nvarchar(1)) Insert t2 select 1,1,1,N'E' union all select 4,2,3,N'F' Go update t1 set [1]=case when [字段值]=1 then [Value] else [1] end, [2]=case when [字段值]=2 then [Value] else [2] end, [3]=case when [字段值]=3 then [Value] else [3] end, [4]=case when [字段值]=4 then [Value] else [4] end from t2 where t1.ID=t2.ID and t1.[MONTH]=t2.[MONTH] select * from t1 /* ID MONTH 1 2 3 4 ----------- ----------- ---- ---- ---- ---- 1 1 E B C D 2 1 A B C D 3 1 A B C D 4 2 A B F D (4 row(s) affected) */
------解决方案--------------------
declare @表2 table (ID int,MONTH int,字段值 int,Value varchar(1)) insert into @表2 select 1,1,1,'E' union all select 4,2,3,'F' select 'update 表1 set ['+ltrim(字段值)+']='''+ Value+''' where ID='+ltrim(ID)+' and MONTH='+ltrim(Month) from @表2 /* update 表1 set [1]='E' where ID=1 and MONTH=1 update 表1 set [3]='F' where ID=4 and MONTH=2 */