SQL面试题（七）

3 个表如下示,一个项目有多个合同，一个合同有多个收款记录
建表：

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：
   每个项目的和同款总数结果
Sql：

SELECT a.id, a.name, SUM(b.hetongkuan) FROM project AS a, hetong AS b
WHERE a.id = b.projectid GROUP BY b.projectid;

问题2：

   每个项目已收款总数结果

Sql：

SELECT a.id, a.name, b.hetongkuan, SUM(c.money) AS moneys FROM project AS a, hetong AS b, inmoney AS c
WHERE a.id = b.projectid AND b.id = c.hetongid GROUP BY c.hetongid;

问题3：
   每个项目合同总数，已收总数结果
Sql：

SELECT * FROM inmoney;
SELECT d.id, d.name, SUM(d.hetongkuan) AS kuangs, SUM(d.moneys) AS moneys FROM (
SELECT a.id, a.name, b.hetongkuan, SUM(c.money) AS moneys FROM project AS a, hetong AS b, inmoney AS c
WHERE a.id = b.projectid AND b.id = c.hetongid GROUP BY c.hetongid) AS d
GROUP BY d.id;