SQL难句，字段值更新解决方案

SQL难句，字段值更新
如图：表1
  ID MONTH [1] [2] [3] [4]
  1 1 A B C D  
  2 1 A B C D  
  3 1 A B C D  
  4 2 A B C D  


 表2
  ID MONTH 字段值 Value
  1 1 1 E
  4 2 3 F

怎样可以跟据表1，跟表2的ID，MONTH关连，修改 表1中字段值为[1]字段改成E。。。
结果：
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

 

[解决办法]

SQL code

if not object_id('t1') is null    drop table t1GoCreate table t1([ID] int,[MONTH] int,[1] nvarchar(1),[2] nvarchar(1),[3] nvarchar(1),[4] nvarchar(1))Insert t1select 1,1,N'A',N'B',N'C',N'D' union allselect 2,1,N'A',N'B',N'C',N'D' union allselect 3,1,N'A',N'B',N'C',N'D' union allselect 4,2,N'A',N'B',N'C',N'D'Goif not object_id('t2') is null    drop table t2GoCreate table t2([ID] int,[MONTH] int,[字段值] int,[Value] nvarchar(1))Insert t2select 1,1,1,N'E' union allselect 4,2,3,N'F'Goupdate t1set [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] endfrom t2where t1.ID=t2.ID and t1.[MONTH]=t2.[MONTH]select *from t1/*ID          MONTH       1    2    3    4----------- ----------- ---- ---- ---- ----1           1           E    B    C    D2           1           A    B    C    D3           1           A    B    C    D4           2           A    B    F    D(4 row(s) affected)*/
[解决办法]
SQL code
declare @表2 table (ID int,MONTH int,字段值 int,Value varchar(1))insert into @表2select 1,1,1,'E' union allselect 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=1update 表1 set [3]='F' where ID=4 and MONTH=2*/