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 ,'数据库', 5create 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
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 ,'数据库', 3create 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
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
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
---------------------------------------------------------------- -- 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 行受影响)*/
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 ,'数据库', 5create table #prize(id int,pname nvarchar(10),pnum int) insert #prize select 1 ,'CET4', 1 insert #prize select 3 ,'CET6', 2select 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
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 5create 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 ,'数据库', 3create 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 行受影响)---写错数据了,这回对了
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
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
-- 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 行受影响)*/
insert #score select 1 ,'SQL', 4
insert #score select 2 ,'数据库', 5create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2select 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 行受影响)
数据输入错误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 ,'数据库', 3create table #prize(id int,pname nvarchar(10),pnum int)
insert #prize select 1 ,'CET4', 1
insert #prize select 3 ,'CET6', 2select 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 行受影响)