一个关于SQL的笔试题目及解答

Mysql实现版本：

create table project(id varchar(10),name varchar(10));
create table hetong(id varchar(10),projectid varchar(10),hetongkuan varchar(10));
create table inmoney(id varchar(10),hetongid varchar(10),money varchar(10));

insert into project values("1","pro1");
insert into project values("2","pro2");
insert into project values("3","pro3");

insert into hetong values("1","1",1000);
insert into hetong values("2","1",2000);
insert into hetong values("3","2",3000);

insert into inmoney values("1","1",500);
insert into inmoney values("2","1",300);
insert into inmoney values("3","2",200);
insert into inmoney values("4","2",300);
insert into inmoney values("5","3",100);

一个项目有多个合同，一个合同有多个收款记录

(1)每个项目的和同款总数结果
select project.id,project.name,sum(hetong.hetongkuan)
from project left join hetong on project.id=hetong.projectid
group by hetong.projectid
order by project.id;
--------------------------
编号  名称      总合同款
1     pro1      3000
2     pro2      3000
3     pro3      null
--------------------------

(2)每个项目已收款总数结果
select project.id,project.name,sum(inmoney.money)
from project,hetong,inmoney
where project.id=hetong.projectid and hetong.id=inmoney.hetongid
group by hetong.projectid;
---------------------------
编号  名称      合同已收款
1     pro1      1300
2     pro2      100
3     pro3      null
---------------------------

(3)每个项目合同总数，已收总数结果

select aa.id,aa.name,sum(aa.hetongkuan),sum(aa.a) from (select project.id,project.name,hetong.hetongkuan,sum(inmoney.money) as a
                                                        from project,hetong,inmoney
                                                        where project.id=hetong.projectid and hetong.id=inmoney.hetongid
                                                        group by inmoney.hetongid) as aa
group by aa.id                                     

---------------------------
编号  名称    总合同款  合同已收款
1     pro1     3000         1300
2     pro2     3000         100
---------------------------

Oracle实现版本：

drop table project;
drop table hetong;
drop table inmoney;
create table project(id varcha2r(10),name varchar2(10));
create table hetong(id varchar2(10),projectid varchar2(10),hetongkuan number(10));
create table inmoney(id varchar(10),hetongid varchar2(10),money number(10));

insert into project values('1','pro1');
insert into project values('2','pro2');
insert into project values('3','pro3');

insert into hetong values('1','1',1000);
insert into hetong values('2','1',2000);
insert into hetong values('3','2',3000);

insert into inmoney values('1','1',500);
insert into inmoney values('2','1',300);
insert into inmoney values('3','2',200);
insert into inmoney values('4','2',300);
insert into inmoney values('5','3',100);

(1)
select p.id,p.name,sum(h.hetongkuan)
from project p
left outer join hetong h
on p.id=h.projectid
group by p.id,p.name;

(2)
select project.id,project.name,sum(inmoney.money)
from project,hetong,inmoney
where project.id=hetong.projectid and hetong.id=inmoney.hetongid
group by project.id,project.name;

(3)
select aa.id,aa.name,sum(aa.hetongkuan),sum(aa.a)  from (select p.id,p.name,h.hetongkuan,sum(m.money) as a
                                                        from project p,hetong h,inmoney m
                                                        where p.id=h.projectid and h.id=m.hetongid
                                                        group by p.id,p.name,h.hetongkuan) aa
group by aa.id,aa.name;