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;
分享到:
相关推荐
SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题
SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题
SQL面试题(很全,带答案),而且整理过的。
sql面试题 软件测试 含答案
经典数据库面试题,提供给数据库面试人员进行面试前练习
sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题
java 面试题基础题,sql 面试题 带答案
SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题SQL面试题
2018_BAT的55道sql面试题2018_BAT的55道sql面试题2018_BAT的55道sql面试题2018_BAT的55道sql面试题2018_BAT的55道sql面试题2018_BAT的55道sql面试题2018_BAT的55道sql面试题
oracle sql 面试题,面试题
很好的sql面试题哦 很细的几乎都包括了面试时候会问所以问题
SQLServer面试题SQLServer面试题SQLServer面试题SQLServer面试题SQLServer面试题
常用的SQL面试题 SQL经典试题 SQL SQL常见笔试试题
最新sql面试题
几个SQL面试题及答案.sql
sql 面试题 很好的sql面试题哦,DBA面试的题目, 内容很丰富,面试中会出现考的问题都有,掌握了面试就没问题了
尚硅谷大数据技术之企业SQL面试题 doc文档,奥利给
SQLServer高频面试题及答案
收录的sql server 面试题大全!!!
一套经典的SQL面试题以及答案一套经典的SQL面试题以及答案