【新手求助】对两张表的某一字段进行求和

[ka10086]

昨天问了个相似的问题，今天这个问题是加强版的~希望前辈们多多指点

两张表：成绩（score），奖项（prize）

成绩表(score)

--------------------------------------------------------------------------------------
学号id 课程名称cname 课程学分cnum
--------------------------------------------------------------------------------------
1 数据库 5
1 SQL 4
2 数据库 3



奖项表（prize）

---------------------------------------------------------------------------------------
学号id 奖项名称pname 奖项学分pnum
---------------------------------------------------------------------------------------
1 CET4 1
3 CET6 2


按照学号id，统计总学分，总学分=课程学分总和+奖项学分。若结果中的奖项名称不存在，则显示“无”。

就是要得到如下的结果：

---------------------------------------------------------------------------------------
学号id 奖项名称pname 总学分
---------------------------------------------------------------------------------------
1 CET4 10
2 无 3
3 CET6 2


请各位多多指点，谢谢了

[东那个升]

create table #score(id int,cname nvarchar(10),cnum int)

insert #score select 1 ,'数据库', 5
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 5

create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2


select a.id,ISNULL(pname,'无') as pname,a.总学分 from (
				select ID,SUM(cnum) as 总学分 from(
				select * from #score
				union all
				select * from #prize
				) a 
				group by id
			) a
		left join #prize b on a.id=b.id
		
		id          pname      总学分
----------- ---------- -----------
1           CET4       10
2           无          5
3           CET6       2

(3 行受影响)

怎么不对。。。

[东那个升]

create table #score(id int,cname nvarchar(10),cnum int)

insert #score select 1 ,'数据库', 5
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 3

create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2


select a.id,ISNULL(pname,'无') as pname,a.总学分 from (
				select ID,SUM(cnum) as 总学分 from(
				select * from #score
				union all
				select * from #prize
				) a 
				group by id
			) a
		left join #prize b on a.id=b.id
		
id          pname      总学分
----------- ---------- -----------
1           CET4       10
2           无          3
3           CET6       2

(3 行受影响)

---写错数据了，这回对了

[-狙击手-]

直接unionall一下就行了

[--小F--]

select
  isnull(a.学号id ,b.学号id) as 学号id ,
  isnull(b.奖项名称pname,'无') as 奖项名称pname,
  a.课程学分cnum +b.奖项学分pnumfrom 
from
  (select 学号id ,sum(课程学分cnum) as 课程学分cnum from score group  by 学号id )a
full join
  (select 学号id ,sum(奖项学分pnum) as 奖项学分pnumfrom score group  by 学号id )b
on
  a.学号id =b.学号id 

[百年树人]

---测试数据---
if object_id('[score]') is not null drop table [score]
go
create table [score]([id] int,[cname] varchar(6),[cnum] int)
insert [score]
select 1,'数据库',5 union all
select 1,'SQL',4 union all
select 2,'数据库',3
if object_id('[prize]') is not null drop table [prize]
go
create table [prize]([id] int,[pname] varchar(4),[pnum] int)
insert [prize]
select 1,'CET4',1 union all
select 3,'CET6',2
 
---查询---
select
  id,
  isnull(max(pname),'无') as pname,
  sum(pnum) as 总学分
from
  (select * from prize union all select id,null,cnum from score) t
group by 
  id

---结果---
id          pname 总学分
----------- ----- -----------
1           CET4  10
2           无     3
3           CET6  2

[gxjwqm]

select isnull(a.id,b.id),
isnull(pname,'无') pname,
isnull(cnum,0)+isnull(pnum,0) as num from 
(select id, sum(cnum) cnum from score group by id) a
full join prize b on  a.id=b.id 

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-03-20 13:30:53
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[score]
if object_id('[score]') is not null drop table [score]
go 
create table [score]([学号id] int,[课程名称cname] varchar(6),[课程学分cnum] int)
insert [score]
select 1,'数据库',5 union all
select 1,'SQL',4 union all
select 2,'数据库',3
--> 测试数据：[prize]
if object_id('[prize]') is not null drop table [prize]
go 
create table [prize]([学号id] int,[奖项名称pname] varchar(4),[奖项学分pnum] int)
insert [prize]
select 1,'CET4',1 union all
select 3,'CET6',2
--------------开始查询--------------------------
select
  isnull(a.学号id ,b.学号id) as 学号id ,
  isnull(b.奖项名称pname,'无') as 奖项名称pname,
  isnull(a.课程学分cnum,0) +isnull(b.奖项学分pnum,0) as 总学分

from
  (select 学号id ,sum(课程学分cnum) as 课程学分cnum from score group  by 学号id )a
full join
  prize b
on
  a.学号id =b.学号id
order by 1
----------------结果----------------------------
/* 学号id        奖项名称pname 总学分
----------- --------- -----------
1           CET4      10
2           无         3
3           CET6      2

(3 行受影响)

*/

[ws_hgo]

create table #score(id int,cname nvarchar(10),cnum int)

insert #score select 1 ,'数据库', 5
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 5

create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2

select  isnull(s.id,p.id) pid,
        p.pname,
        isnull(cnum,0)+isnull(pnum,0) 总学分
from
(
 select id,pname,sum(pnum) pnum from #prize group by id,pname
) P
full join
(
 select id,'' as cname,sum(cnum) cnum from #score group by id
) S
on P.id=s.id
         
pid         pname      总学分
----------- ---------- -----------
1           CET4       10
2           NULL       5
3           CET6       2

(3 行受影响)



      

[ws_hgo]

不好意思
数据输入错误

create table #score1(id int,cname nvarchar(10),cnum int)

insert #score1 select 1 ,'数据库', 5
insert #score1 select 1 ,'SQL', 4
insert #score1 select 2 ,'数据库', 3

create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2

select  isnull(s.id,p.id) pid,
        p.pname,
        isnull(cnum,0)+isnull(pnum,0) 总学分
from
(
 select id,pname,sum(pnum) pnum from #prize group by id,pname
) P
full join
(
 select id,'' as cname,sum(cnum) cnum from #score1 group by id
) S
on P.id=s.id
         

pid         pname      总学分
----------- ---------- -----------
1           CET4       10
2           NULL       3
3           CET6       2

(3 行受影响)


      

[yhtapmys]

[Quote=引用 2 楼 ldslove 的回复:]
SQL code

create table #score(id int,cname nvarchar(10),cnum int)

insert #score select 1 ,'数据库', 5
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 3

create table #prize(id in……
[/Quote]
up

[ka10086]

非常感谢各位，又学习到了很多新知识~