declare @t1 table(id int ,f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10))
insert @t1
select 1, 'A', 'D', 'C', 'A' union all
select 2, 'A', 'A', 'D', 'C' union all
select 3, 'B', 'C', 'B', 'A' union all
select 4, 'C', 'B', 'A', 'B' union all
select 5, 'A', 'C', 'B', 'A' union all
select 6, 'D', 'A', 'B', 'A'
declare @t2 table(f1 varchar(10),f2 int)
insert @t2
select 'A', 5 union all
select 'B', 4 union all
select 'C', 3 union all
select 'D', 2
select sum(a.f2),sum(b.f2),sum(c.f2),sum(d.f2)
from @t1 t
inner join @t2 a on t.f1 = a.f1
inner join @t2 b on t.f2 = b.f1
inner join @t2 c on t.f3 = c.f1
inner join @t2 d on t.f4 = d.f1
update #t
set 字段1= 表2.字段2
from #t, 表2
where #t.字段1 = 表2.字段1
update #t
set 字段2= 表2.字段2
from #t, 表2
where #t.字段2 = 表2.字段2
update #t
set 字段3= 表2.字段2
from #t, 表2
where #t.字段3 = 表2.字段2
update #t
set 字段4= 表2.字段2
from #t, 表2
where #t.字段4 = 表2.字段2
declare @t1 table(id int ,f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10))
insert @t1
select 1, 'A', 'D', 'C', 'A' union all
select 2, 'A', 'A', 'D', 'C' union all
select 3, 'B', 'C', 'B', 'A' union all
select 4, 'C', 'B', 'A', 'B' union all
select 5, 'A', 'C', 'B', 'A' union all
select 6, 'D', 'A', 'B', 'A'
declare @t2 table(f1 varchar(10),f2 int)
insert @t2
select 'A', 5 union all
select 'B', 4 union all
select 'C', 3 union all
select 'D', 2
select t.*, total = a.f2 + b.f2 + c.f2 + d.f2
from @t1 t
inner join @t2 a on t.f1 = a.f1
inner join @t2 b on t.f2 = b.f1
inner join @t2 c on t.f3 = c.f1
inner join @t2 d on t.f4 = d.f1
/*结果
id total
---------------------------
1 15
2 15
3 16
4 16
5 17
6 16
*/
create table test1(ID int identity(1,1),字段1 char(1),字段2 char(1),字段3 char(1),字段4 char(1))
insert test1(字段1,字段2,字段3,字段4)
select 'A','D','C','A' union all
select 'A','A','D','C' union all
select 'B','C','B','A' union all
select 'C','B','A','B' union all
select 'A','C','B','A' union all
select 'D','A','B','A'
select * from test1
create table test2(字段1 char(1),字段2 int)
insert test2
select 'A',5 union all
select 'B',4 union all
select 'C',3 union all
select 'D',2
--select * from test2
select
sum(a.字段2) as 字段1,
sum(b.字段2) as 字段1,
sum(c.字段2) as 字段1,
sum(d.字段2) as 字段1
from test1 i,test2 a,test2 b,test2 c,test2 d
where i.字段1=a.字段1 and i.字段2=b.字段1 and i.字段3=c.字段1 and i.字段4=d.字段1