SQL整理十

项目表：project
合同表：hetong
收款表：inmoney
SQL:
+---------------------------------------------------------------------------------+
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 a.name,sum(b.hetongkuan) as sum| 
|from project a,hetong b               |
|where a.id=b.projectid                |
|group by b.projectid;                 |
+--------------------------------------+
2.每个项目已收款总数结果
+--------------------------------------+
|SELECT a.name,sum(c.money) as sum     |
|from project a,hetong b,inmoney c     |
|where a.id=b.projectid                |
|and b.id=c.hetongid                   |
|group by b.projectid;                 |
+--------------------------------------+
3.每个项目合同总数，已收总数结果
+--------------------------------------------------------------+
|select e.id,sum(e.hetongkuan) as total,sum(sum_in) as in_money| 
|from (SELECT a.id,a.name,b.hetongkuan,sum(c.money) as sum_in  |
| from project a,hetong b,inmoney c                            |
| where a.id=b.projectid and b.id=c.hetongid                   |
| group by c.hetongid) e                                       |
|group by e.id;                                                |
+--------------------------------------------------------------+