交叉求和，请好心人来给小弟弟帮帮忙，就先在此谢过
如下是2个表结构：
表1：
NameID Score
----------- -----------
1001 20
1002 32

表2：
NameID1 Score1 NameID2 Score2 NameID3 Score3
----------- ----------- ----------- ----------- ----------- -----------
1001 5 1002 2 1003 6
1002 6 1003 4 1001 2

问题》：
从表1取一个NameID
然后根据表1的nameid值到表2找出所有nameid相同的和

如:表1：NameID=1001
表2：Score=7(NameID1=1001 NameID3=1001)
以下代码问题是：只显示最后一条记录：
Declare @nameID int
Set @nameID=0
select @nameID=NameID from table1

select @nameID As NameIDIn,Score=Score1+Score2+Score3
FROM
(
select count(*) as total,sum(case when NameID1=@nameID then Score1 else 0 end) As Score1,
sum(case when NameID2=@nameID then Score2 else 0 end) As Score2,
sum(case when NameID3=@nameID then Score3 else 0 end) As Score3

from table2
)
as temp

[解决办法]
declare @NameID int
set @NameID = 1001
select NameID = @NameID, Score = ((select isnull(sum(Score1),0) from table2 where NameID1 = @NameID)+(select isnull(sum(Score2),0) from table2 where NameID2 = @NameID)+(select isnull(sum(Score3),0) from table2 where NameID3 = @NameID))

[解决办法]
create table t1(
NameID varchar(10),
Score int)
insert t1 select '1001 ',20
union all select '1002 ',32
union all select '1003 ',32
create table t2(
NameID1 varchar(10),
Score1 int,
NameID2 varchar(10),
Score2 int,
NameID3 varchar(10),
Score3 int)
insert t2 select '1001 ',5, '1002 ',2, '1003 ',6
union all select '1002 ',6, '1003 ',4, '1001 ',2

select t1.NameID,sum(t.Score) as ScoreSum from(
select NameID1 as NameID,Score1 as Score from t2
union all
select NameID2,Score2 from t2
union all
select NameID3,Score3 from t2
) t,t1 where t.NameID=t1.NameID group by t1.NameID

--结果：
/*
NameID ScoreSum
---------- -----------
1001 7
1002 8
1003 10
*/
------解决方案--------------------

select NameID, sum(score) from
(select NameID1 as NameID,Score1 as Score from t2 union
select NameID2 as NameID,Score2 as Score from t2 union
select NameID3 as NameID,Score3 as Score from t2 ) a
where a.NameID in (select NameID from t1) group by NameID
[解决办法]
create table 表1(NameID int,Score int)
insert into 表1 values(1001, 20)
insert into 表1 values(1002, 32)
create table 表2(NameID1 int,Score1 int,NameID2 int,Score2 int,NameID3 int,Score3 int)
insert into 表2 values(1001, 5, 1002, 2, 1003, 6)
insert into 表2 values(1002, 6, 1003, 4, 1001, 2)

select a.nameid , sum(b.score) score from 表1 a,
(
select nameid1 nameid,score1 score from 表2
union all
select nameid2 nameid,score2 score from 表2
union all
select nameid3 nameid,score3 score from 表2
) b
where a.nameid = b.nameid
group by a.nameid

drop table 表1,表2
/*
nameid score
----------- -----------
1001 7
1002 8

（所影响的行数为 2 行）
*/
[解决办法]
--如果表1有,表2没有的数据也求和,需要用left join

create table 表1(NameID int,Score int)
insert into 表1 values(1001, 20)
insert into 表1 values(1002, 32)
insert into 表1 values(1009, 32)
create table 表2(NameID1 int,Score1 int,NameID2 int,Score2 int,NameID3 int,Score3 int)
insert into 表2 values(1001, 5, 1002, 2, 1003, 6)
insert into 表2 values(1002, 6, 1003, 4, 1001, 2)

select a.nameid , isnull(sum(b.score),0) score from 表1 a
left join
(
select nameid1 nameid,score1 score from 表2
union all
select nameid2 nameid,score2 score from 表2
union all
select nameid3 nameid,score3 score from 表2
) b
on a.nameid = b.nameid
group by a.nameid

drop table 表1,表2
/*
nameid score
----------- -----------
1001 7
1002 8
1009 0

（所影响的行数为 3 行）
*/
[解决办法]
将表2 三组列分解为一个一组列的表
再对此表查询
select nameid,sum(score)
from
(select nameid1 nameid,score1 score from 表2
union all select nameid2 nameid,score2 score from 表2
union all select nameid3 nameid,score3 score from 表2) as temp
group by nameid
以上查询可得出所有nameid的分数和
[解决办法]
查询的结果存放在表3，如果表3有记录就更新表3，如果表3没有就插入一条新记录到表3，使用存储过程完成。


Create Procedure dbo.testprocedure
As
Insert into table3
select nameid ,score=sum(cont) from(
select a.nameid,cont=(
(case nameid1 when a.nameid then score1 else 0 end)+
(case nameid2 when a.nameid then score2 else 0 end)+
(case nameid3 when a.nameid then score3 else 0 end) )
from table1 a,table2) as t group by nameid


使用这个存储过程会产生重复记录
----------------------

Create Procedure dbo.testprocedure
As
Insert into table3
select nameid ,score=sum(cont) from(
select a.nameid,cont=(
(case nameid1 when a.nameid then score1 else 0 end)+
(case nameid2 when a.nameid then score2 else 0 end)+
(case nameid3 when a.nameid then score3 else 0 end) )
from table1 a,table2) as t
where nameid not in (select nameid from table3)
group by nameid

[解决办法]
create proc test
@NameID int
as
set nocount on
if exists (select 1 from 表3 where nameid = @nameid)
return
insert 表3 select @NameID, ((select isnull(sum(Score1),0) from 表2 where NameID1 = @NameID)+(select isnull(sum(Score2),0) from 表2 where NameID2 = @NameID)+(select isnull(sum(Score3),0) from 表2 where NameID3 = @NameID))
set nocount off
go
[解决办法]
给个列不确定的情况...

临时表
select cool=right([name],1),tname=(case when charindex( 'Name ',[name])> 0 then [name] end),
tsc=(case when charindex( 'Score ',[name])> 0 then [name] end)
into #temp from (select * from syscolumns
where object_id( 'table2 ')=id) k

执行SQL
--------------------------------------------------
declare @sql varchar(1000)
select @sql= ' '

select @sql=@sql+ ' select '+tname+ ', '+tsc+ ' from table2 union all '
from (select cool,max(tname) as tname,Max(tsc) as tsc from #temp group by cool) j
set @sql=left(@sql,len(@sql)-10)
set @sql= 'select sum(Score1) from ( '+@sql+ ') k where Name1= ' '1001 ' ' '
execute(@sql)


在列不确定情况下皆可行
[解决办法]
Create Procedure dbo.testprocedure
As
Insert into test3
select nameid ,score=sum(cont) from(
select a.nameid,cont=(
(case nameid1 when a.nameid then score1 else 0 end)+
(case nameid2 when a.nameid then score2 else 0 end)+
(case nameid3 when a.nameid then score3 else 0 end) )
from test1 a,test2 where nameid not in(select nameid from test3)) as t group by nameid

这样就没重复的了