场景：有3张金融表，求出符合要求的结果集，已有SQL，请教能否在优化，采纳者赠送全部pub币！
合同金额表 finances_sum
IID主键 COPERATIONID合同业务ID CURRENCYKINDCODE币种 validityflag数据有效状态标志
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
合同表 finances_con
IID主键 COPERATIONID合同业务ID contractinuredate合同生效日期 contractpausedate合同终止日期 validityflag数据有效状态标志
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
合同租金表 finances_ren
IID主键 COPERATIONID合同业务ID RENTALSUM本期实还金额 validityflag数据有效状态标志
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
【问】
想得到的一个结果集,前4个字段是合同金额表finances_sum的，后2个字段是合同表finances_con的，最后1个字段是合同租金表finances_ren的
IID主键 COPERATIONID合同业务ID CURRENCYKINDCODE币种 validityflag数据有效状态标志 contractinuredate合同生效日期 contractpausedate合同终止日期 MAX(RENTALSUM本期实还金额)
185 62 CNY 1 2014-1-1 2014-12-31 500000
186 62 ENU 1 2014-1-1 2014-12-31 500000
187 61 CNY 1 2014-1-1 2014-12-31 500000
188 61 ENU 1 2014-1-1 2014-12-31 500000
189 60 CNY 1 2014-1-1 2014-12-31 50
190 59 CNY 1 2014-1-1 2014-12-31 300000
191 58 CNY 1 2014-1-1 2014-12-31 400000
192 57 CNY 1 2010-3-8 2010-5-8 500
【答】
根据上述要求，分2条语句来实现
第1条SQL，前6个字段结果集，涉及到合同金额表finances_sum和合同表finances_con
select a.*,b.contractinuredate,b.contractpausedate from finances_sum a,finances_con b where a.validityflag = 1 and a.coperationid=b.coperationid;
IID主键 COPERATIONID合同业务ID CURRENCYKINDCODE币种 validityflag数据有效状态标志 contractinuredate合同生效日期 contractpausedate合同终止日期
185 62 CNY 1 2014-1-1 2014-12-31
186 62 ENU 1 2014-1-1 2014-12-31
187 61 CNY 1 2014-1-1 2014-12-31
188 61 ENU 1 2014-1-1 2014-12-31
189 60 CNY 1 2014-1-1 2014-12-31
190 59 CNY 1 2014-1-1 2014-12-31
191 58 CNY 1 2014-1-1 2014-12-31
192 57 CNY 1 2010-3-8 2010-5-8
第2条SQL，后面求“在合同业务ID中本期实还金额最大的记录”，涉及到合同租金表finances_ren
select coperationid,max(rentalsum) from finances_ren where validityflag =1 group by coperationid;
IID主键 COPERATIONID合同业务ID RENTALSUM本期实还金额 validityflag数据有效状态标志
300 62 500000 1
303 61 500000 1
306 60 50 1
309 59 300000 1
312 58 400000 1
315 57 500 1
【终问】现在想把上述这2条SQL合并成1条SQL来实现，具体如何操作
重现问题脚本（包括建表和插入数据代码）
####################################################################################
--简化版重现问题模型，建表和构建数据脚本如下所示
--合同金额表 finances_sum
drop table finances_sum cascade constraints;
/*==============================================================*/
/* Table: finances_sum */
/*==============================================================*/
create table finances_sum (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CURRENCYKINDCODE VARCHAR2(5),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_sum primary key (IID)
);
insert into finances_sum values(183,61,'ENU','2');
insert into finances_sum values(184,62,'CNY','2');
insert into finances_sum values(185,62,'CNY','1');
insert into finances_sum values(186,62,'ENU','1');
insert into finances_sum values(187,61,'CNY','1');
insert into finances_sum values(188,61,'ENU','1');
insert into finances_sum values(189,60,'CNY','1');
insert into finances_sum values(190,59,'CNY','1');
insert into finances_sum values(191,58,'CNY','1');
insert into finances_sum values(192,57,'CNY','1');
commit;
select * from finances_sum;
IID主键 COPERATIONID合同业务ID CURRENCYKINDCODE币种 validityflag数据有效状态标志
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
--合同表 finances_con
drop table finances_con cascade constraints;
/*==============================================================*/
/* Table: finances_con */
/*==============================================================*/
create table finances_con (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CONTRACTINUREDATE DATE,
CONTRACTPAUSEDATE DATE,
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_con primary key (IID)
);
insert into finances_con values(200,62,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(201,61,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(202,60,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(203,59,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(204,58,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(205,57,to_date('2010-3-8', 'yyyy-mm-dd'),to_date('2010-5-8', 'yyyy-mm-dd'),'1');
commit;
select * from finances_con;
IID主键 COPERATIONID合同业务ID contractinuredate合同生效日期 contractpausedate合同终止日期 validityflag数据有效状态标志
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
--合同租金表 finances_ren
drop table finances_ren cascade constraints;
/*==============================================================*/
/* Table: finances_ren */
/*==============================================================*/
create table finances_ren (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
RENTALSUM VARCHAR2(20),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_ren primary key (IID)
);
insert into finances_ren values(298,61,'500000','2');
insert into finances_ren values(299,62,'500000','2');
insert into finances_ren values(300,62,'500000','1');
insert into finances_ren values(301,62,'400000','1');
insert into finances_ren values(302,62,'300000','1');
insert into finances_ren values(303,61,'500000','1');
insert into finances_ren values(304,61,'400000','1');
insert into finances_ren values(305,61,'300000','1');
insert into finances_ren values(306,60,'50','1');
insert into finances_ren values(307,60,'40','1');
insert into finances_ren values(308,60,'30','1');
insert into finances_ren values(309,59,'300000','1');
insert into finances_ren values(310,59,'200000','1');
insert into finances_ren values(311,59,'100000','1');
insert into finances_ren values(312,58,'400000','1');
insert into finances_ren values(313,58,'10000','1');
insert into finances_ren values(314,58,'10000','1');
insert into finances_ren values(315,57,'500','1');
insert into finances_ren values(316,57,'400','1');
insert into finances_ren values(317,57,'300','1');
insert into finances_ren values(318,57,'100','1');
commit;
select * from finances_ren;
IID主键 COPERATIONID合同业务ID RENTALSUM本期实还金额 validityflag数据有效状态标志
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
####################################################################################
答案：
1.select a.*,b.contractinuredate,b.contractpausedate,c.rentalsum
from finances_sum a,finances_con b,(select coperationid,max(rentalsum) rentalsum from finances_ren where validityflag =1 group by coperationid) c
where a.validityflag = 1 and a.coperationid=b.coperationid and a.coperationid=c.coperationid;
小结：数据量c表是b表的6倍，b表是a表的2倍，请见执行计划，c表的肯定在a表中有，a表的不一定在c表中有，因此一般group by操作的可能性大一点。当数据量上来后后续就可以添加索引来测试了
Execution Plan
----------------------------------------------------------
Plan hash value: 3900923520
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 356 | 11 (19)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 356 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 280 | 8 (25)| 00:00:01 |
| 3 | VIEW | | 4 | 100 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 4 | 56 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EB_FINANCINGLEASE_RENTAL | 8 | 112 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CONSUM | 6 | 270 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CON | 6 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
2.select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate,max(c.rentalsum) max_rentalsum from finances_sum a,finances_con b,finances_ren c
where a.coperationid = b.coperationid
and a.coperationid = c.coperationid
and a.validityflag = '1'
and c.validityflag = '1'
group by a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 989 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 989 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 989 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 256 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 114 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
3.select t1.iid,t1.COPERATIONID,t1.CURRENCYKINDCODE,t1.VALIDITYFLAG,t1.contractinuredate,t1.contractpausedate,t2.max_rentalsum from
(select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate from finances_sum a, finances_con b where a.validityflag = '1' and a.coperationid = b.coperationid) t1,
(select coperationid,max(rentalsum) max_rentalsum from finances_ren where validityflag = '1' group by coperationid) t2 where t1.coperationid = t2.coperationid order by t1.iid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1104 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 1104 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 1104 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 296 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 144 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Leonarding刘盛
2014.06.18
北京&summer
分享技术~成就梦想
Blog：www.leonarding.com