【话题讨论】有场景有数据，采纳者赠送全部pub币，速进

leonarding

场景：有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来实现，具体如何操作

重现问题脚本（包括建表和插入数据代码）
test_script.sql (6.78 KB, 下载次数: 44)

2014-6-19 12:27 上传

点击文件名下载附件

          请点击下载





18#苏上人 SQL执行结果报错，请见下图， group by a.*  (不能有星符吧)

ZALBB

盛哥，到开发版发帖，那里的大师SQL优化水平更高，

bbshenlei

在最后加个相关子查询就可以了吧，select a.* ,(select max(rentalsum) from finances_ren c where validityflag =1 and c.coperationid=a.coperationid) from xxx a;

bfc99

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;

初识orcl

盛哥，去开发板吧，有newkid大师在，还有兔子等人，绝对靠谱，newkid正好后半夜上线。

Okhotsk

顶一顶

psufnxk2000

两个 sql通过 ，coperationid 关联 起来。

xgghxkhuang

你三个表数据量各多大，索引情况如何？
其实你第一个sql和第二个sql根据合同号关联就能合并，但是优化得看你的sql数据量，索引什么的

xgghxkhuang

xgghxkhuang 发表于 2014-6-17 21:10
你三个表数据量各多大，索引情况如何？
其实你第一个sql和第二个sql根据合同号关联就能合并，但是优化得看 ...

当你第一个结果集过滤出来足够小第二个上面有索引可以用相关子查询，否则先聚合再链接也行，你得看如何做能尽快过滤掉数据

oracle_cj

lz，全部pub币有多少啊