我有T1,T2,T_Join表.
T_Join：第一列：ID(唯一)例如：10,11,12,13.第二列：CODE,它包含的属性等于T2的列名.例如：类型,来源,部分,重要性.这些由T1中的ID标识.据此,属性’source’的ID是11.
ID CODE 10 type 11 source 12 section 13 importance
在表T1中,第一列是不唯一的data_ID：1020,1020,1022,1023,1028,1035,1035等.
第二列是来自T_Join的ID.在此示例中,4 ID可以属于1 data_ID,这些声明的值出现在第三列(VALUE)中：
data_ID ID VALUE 1020 10 1 1020 11 123 1020 12 9 1020 13 4 1022 10 2 1022 12 15 1023 10 2 1023 11 108 1023 13 2 1028 12 20
…
这意味着具有ID 1020的项目是类型1,源自源No.123,该实际对象由存储在第9节中的该ID识别并具有第四级重要性.
现在,我有一张桌子T2.第一列与T1中的data_ID相同.在这张表中,这些是独特的.其他栏目:(多么令人惊讶！)类型,重要性. (实际上,不仅有四个属性,而且至少有五十个！)
所以表看起来像这样：
data_ID type source section importance 1020 1 123 9 2 1022 1 95 3 5 1023 2 108 21 4 1028 1 147 17 5
T2包含较新的数据.我想用这些更新T1.VALUE列.按照上面的示例,更新的T1应如下所示：
data_ID ID VALUE 1020 10 1 1020 11 123 1020 12 9 1020 13 2 1022 10 1 1022 12 3 1023 10 2 1023 11 108 1023 13 4 1028 12 17 ...
因此,在data_ID 1020处,重要性为4并且它变为2,因为在T1中ID为13并且它从T_Join表引用属性“重要性”,依此类推.
我想以这种方式更新所有数据.我不是sql专家,我设法创建了这个代码：
update T1 set VALUE =
(select * from T2
inner join T_Join on ID=
(SELECT
c.name
FROM
sys.objects o
INNER JOIN
sys.columns c
ON
c.object_id = o.object_id
AND o.name = 'T2')
where T1.data_ID = T2.data_ID and T2.ID = T_Join.ID)
from T1
inner join T2 on T1.data_ID = T2.data_ID
inner join T_Join on T1.ID = T_Join.ID
select * from T1
但它不起作用,错误信息：
Msg 116,Level 16,State 1,Line 16
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
我尝试使用CURSOR语句和声明的变量(基于建议)解决它,但它也不起作用.
如果有人知道如何解决这个问题(以最简单的方式),请尽可能详细地回答.
解决方法
首先,您需要对T2表进行反规范化,这将采用列并将其转换为行.在sql Server 2005中,他们引入了UNPIVOT函数,它将为您执行此操作.
第一步是将T2和T_Join中的数据选择为行. SELECT语句是：
select j.id,j.code,u.data_id,u.value
from T_Join j
inner join
(
select data_id,col,value
from T2
unpivot
(
value
for col in (type,source,section,importance)
) unpiv
) u
on j.code = u.col
请参阅SQL Fiddle with Demo.这将获取您的列数据并将其转换为给出结果的行：
| ID | CODE | DATA_ID | VALUE | ------------------------------------- | 10 | type | 1020 | 1 | | 11 | source | 1020 | 123 | | 12 | section | 1020 | 9 | | 13 | importance | 1020 | 2 | | 10 | type | 1022 | 1 | | 11 | source | 1022 | 95 | | 12 | section | 1022 | 3 | | 13 | importance | 1022 | 5 | | 10 | type | 1023 | 2 | | 11 | source | 1023 | 108 | | 12 | section | 1023 | 21 | | 13 | importance | 1023 | 4 | | 10 | type | 1028 | 1 | | 11 | source | 1028 | 147 | | 12 | section | 1028 | 17 | | 13 | importance | 1028 | 5 |
一旦数据采用该格式,您就可以在UPDATE语句中使用它：
update t1
set t1.value = t.value
from t1
inner join
(
select j.id,u.value
from T_Join j
inner join
(
select data_id,value
from T2
unpivot
(
value
for col in (type,importance)
) unpiv
) u
on j.code = u.col
) t
on t1.data_id = t.data_id
and t1.id = t.id;
您声明的下一个问题是您需要大约50个列才能取消.如果是这种情况,那么您可以使用动态sql来获取要转换为行的列列表.您的动态sql脚本将是：
DECLARE @colsUnpivot AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('T2') and
C.name not in ('data_ID')
for xml path('')),1,'')
set @query
= 'update t1
set t1.value = t.value
from t1
inner join
(
select j.id,u.value
from T_Join j
inner join
(
select data_id,value
from T2
unpivot
(
value
for col in ('+@colsUnpivot+')
) unpiv
) u
on j.code = u.col
) t
on t1.data_id = t.data_id
and t1.id = t.id;'
exec(@query);
代码将使用以下结果更新T1：
| DATA_ID | ID | VALUE | ------------------------ | 1020 | 10 | 1 | | 1020 | 11 | 123 | | 1020 | 12 | 9 | | 1020 | 13 | 2 | | 1022 | 10 | 1 | | 1022 | 12 | 3 | | 1023 | 10 | 2 | | 1023 | 11 | 108 | | 1023 | 13 | 4 | | 1028 | 12 | 17 |
