如下是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 table1select @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 Score3from table2
)
as temp
表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 table1select @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 Score3from table2
)
as temp
----------- -----------
1002 8这个是上面代码执行的结果
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))
declare @T1 table (NameID int, Score int)
insert @T1
select 1001, 20 union all
select 1002, 32
declare @T2 table (NameID1 int, Score1 int, NameID2 int, Score2 int, NameID3 int, Score3 int)
insert @T2
select 1001, 5, 1002, 2, 1003, 6 union all
select 1002, 6, 1003, 4, 1001, 2select a.NameID, Score = isnull(a.Score,0)+isnull(b.Score,0)+isnull(c.Score,0)
from
(select a.NameID, Score = sum(b.Score1) from @T1 a, @T2 b where a.NameID = b.NameID1 group by a.NameID) a
left join
(select a.NameID, Score = sum(b.Score2) from @T1 a, @T2 b where a.NameID = b.NameID2 group by a.NameID) b
on a.NameID = b.NameID
left join
(select a.NameID, Score = sum(b.Score3) from @T1 a, @T2 b where a.NameID = b.NameID3 group by a.NameID) c
on a.NameID = c.NameID
/*
NameID Score
1001 7
1002 8
*/
declare @T1 table (NameID int, Score int)
insert @T1
select 1001, 20 union all
select 1002, 32 union all
select 1003, 23 union all
select 1004, 32
declare @T2 table (NameID1 int, Score1 int, NameID2 int, Score2 int, NameID3 int, Score3 int)
insert @T2
select 1001, 5, 1002, 2, 1003, 6 union all
select 1002, 6, 1003, 4, 1001, 2select a.NameID, Score = isnull(a.Score,0)+isnull(b.Score,0)+isnull(c.Score,0)
from
(select a.NameID, Score = sum(b.Score1) from @T1 a left join @T2 b on a.NameID = b.NameID1 group by a.NameID) a
left join
(select a.NameID, Score = sum(b.Score2) from @T1 a left join @T2 b on a.NameID = b.NameID2 group by a.NameID) b
on a.NameID = b.NameID
left join
(select a.NameID, Score = sum(b.Score3) from @T1 a left join @T2 b on a.NameID = b.NameID3 group by a.NameID) c
on a.NameID = c.NameID
/*
NameID Score
1001 7
1002 8
1003 10
1004 0
*/
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',2select 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 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
(
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 nameid
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.nameiddrop table 表1,表2
/*
nameid score
----------- -----------
1001 7
1002 8(所影响的行数为 2 行)
*/
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.nameiddrop table 表1,表2
/*
nameid score
----------- -----------
1001 7
1002 8
1009 0(所影响的行数为 3 行)
*/
表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
表3:
NameIDIn ScoreIn
----------- -----------
问题》:
从表1取一个NameID
然后根据表1的nameid值到表2找出所有nameid相同的和,将计算的和插入到表3。使用存储过程去完成。
@NameID int
as
set nocount on
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 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) as t group by nameid
@NameID varchar(10))
as
insert t3 --假设第3个表为t3
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=@NameID and t.NameID=t1.NameID group by t1.NameID
go
exec p1 @NameID='1002'
select * from t3
drop proc p1
表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
表3:
NameIDIn ScoreIn
----------- -----------
问题》:
从表1取一个NameID
然后根据表1的nameid值到表2找出所有nameid相同的和,将计算的和插入到表3。使用存储过程去完成。
要写在一个存储过程里。
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
使用这个存储过程会产生重复记录
再对此表查询
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的分数和
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 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
@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
表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
表3:
NameIDIn ScoreIn
----------- -----------
问题》:
从表1取一个NameID
然后根据表1的nameid值到表2找出所有nameid相同的和,将计算的和插入到表3。,如果表3有记录就更新表3,如果表3没有就插入一条新记录到表3,使用存储过程完成。使用存储过程去完成。
要写在一个存储过程里。
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)
在列不确定情况下皆可行
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 这样就没重复的了
declare @T1 table (NameID int, Score int)
insert @T1
select 1001, 20 union all
select 1002, 32 union all
select 1003, 23 union all
select 1004, 32
declare @T2 table (NameID1 int, Score1 int, NameID2 int, Score2 int, NameID3 int, Score3 int)
insert @T2
select 1001, 5, 1002, 2, 1003, 6 union all
select 1002, 6, 1003, 4, 1001, 2declare @id int
set @id=1003select
sum(
case
when NameID1=@id then Score1
when NameID2=@id then Score2
when NameID3=@id then Score3
else 0
end
)
from @T2