如图：表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*/