SQL解惑二
谜题51:
构建数据SQL:
点击(此处)折叠或打开
- create or replace view 预算 (项目 ,种类 ,预算费用)
- as
- select 1 , 9100 , 100.00 from dual union all
- select 2 , 9100 , 15.00 from dual union all
- select 3 , 9100 , 6.00 from dual union all
- select 4 , 9200 , 8.00 from dual union all
- select 5 , 9200 , 11.00 from dual;
-
- create or replace view 支出(收据 ,项目, 支出费用)
- as
- select 1 , 1 , 10.00 from dual union all
- select 2 , 1 , 20.00 from dual union all
- select 3 , 1 , 15.00 from dual union all
- select 4 , 2 , 32.00 from dual union all
- select 5 , 4 , 8.00 from dual union all
- select 6 , 5 , 3.00 from dual union all
- select 7 , 5 , 4.00 from dual;
![bb](http://img.blog.itpub.net/blog/attachment/201407/20/29507357_1405859747Yfkh.jpg?x-oss-process=style/bb)
该类问题应该先聚合,然后再进行表的关联
解答如下:
点击(此处)折叠或打开
- select v.种类, v.预算合计, n.支出合计
- from (select y.种类, sum(y.预算费用) as 预算合计
- from 预算 y
- group by y.种类)v
- join
- (select y.种类, sum(支出费用) as 支出合计
- from 预算 y
- join 支出 z
- using (项目)
- group by y.种类) n
- on (v.种类 = n.种类)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29507357/viewspace-1223203/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29507357/viewspace-1223203/