碰到标量子查询中有ROWNUM=1怎么改？
select to_date(o.postdate,'yyyymmdd'), (select cur.c_code from cur_tbl cur where cur.c_abbr=o.currencycode) as CusNo, o.dramount, round(o.dramount * (select s.exechangerate / 100 from tcsa.uccexchange s where s.exchangecurrency = (SELECT T.t_code FROM tcsa.sap_code_mapping t where t.typename = 'currency' and T.sap_code = o.currencycode) and TO_CHAR(s.issuancedate, 'yyyymmdd') = o.postdate and rownum = 1),2) AS Debit_Sum_RMB, o.cramount, round(o.cramount * (select s.exechangerate / 100 from tcsa.uccexchange s where s.exchangecurrency = (SELECT T.t_code FROM tcsa.sap_code_mapping t where t.typename = 'currency' and T.sap_code = o.currencycode) and TO_CHAR(s.issuancedate, 'yyyymmdd') = o.postdate and rownum = 1),2) AS Credit_Sum_RMB from tcsa.mm_sap_voucher_detail_to o where o.postdate >= '20170101' and o.postdate < '20180101' ; Plan hash value: 1961056669 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1210K| 639M| | 153K (1)| 00:30:48 | | | |* 1 | TABLE ACCESS FULL | CUR_TBL | 1 | 12 | | 3 (0)| 00:00:01 | | | |* 2 | COUNT STOPKEY | | | | | | | | | |* 3 | FILTER | | | | | | | | | |* 4 | VIEW | | 31006 | 2240K| | 778 (1)| 00:00:10 | | | |* 5 | WINDOW SORT PUSHED RANK| | 31006 | 2815K| 6632K| 778 (1)| 00:00:10 | | | | 6 | REMOTE | UCCEXCHANGE | 31006 | 2815K| | 111 (1)| 00:00:02 | CLONE | R->S | | 7 | REMOTE | SAP_CODE_MAPPING | 1 | 240 | | 2 (0)| 00:00:01 | CLONE | R->S | |* 8 | COUNT STOPKEY | | | | | | | | | |* 9 | FILTER | | | | | | | | | |* 10 | VIEW | | 31006 | 2240K| | 778 (1)| 00:00:10 | | | |* 11 | WINDOW SORT PUSHED RANK| | 31006 | 2815K| 6632K| 778 (1)| 00:00:10 | | | | 12 | REMOTE | UCCEXCHANGE | 31006 | 2815K| | 111 (1)| 00:00:02 | CLONE | R->S | | 13 | REMOTE | SAP_CODE_MAPPING | 1 | 240 | | 2 (0)| 00:00:01 | CLONE | R->S | | 14 | REMOTE | MM_SAP_VOUCHER_DETAIL_TO | 1210K| 639M| | 153K (1)| 00:30:48 | CLONE | R->S | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CUR"."C_ABBR"=SYS_OP_C2C(:B1)) 2 - filter(ROWNUM=1) 3 - filter("EXCHANGECURRENCY"= (SELECT /*+ */ "T"."t_code" FROM "A2" WHERE "T"."TYPENAME"='currency' AND "T"."SAP_CODE"=:B1)) 4 - filter(TO_CHAR("ISSUANCEDATE",'yyyymmdd')=:B1 AND "RN"=1) 5 - filter(ROW_NUMBER() OVER ( PARTITION BY "T_RMB"."BASECURRENCY","T_RMB"."EXCHANGECURRENCY",TO_DATE(TO_CHAR(INTERN AL_FUNCTION("T_RMB"."ISSUANCEDATE"),'yyyymmdd'),'yyyymmdd') ORDER BY "STATUS")<=1) 8 - filter(ROWNUM=1) 9 - filter("EXCHANGECURRENCY"= (SELECT /*+ */ "T"."t_code" FROM "A2" WHERE "T"."TYPENAME"='currency' AND "T"."SAP_CODE"=:B1)) 10 - filter(TO_CHAR("ISSUANCEDATE",'yyyymmdd')=:B1 AND "RN"=1) 11 - filter(ROW_NUMBER() OVER ( PARTITION BY "T_RMB"."BASECURRENCY","T_RMB"."EXCHANGECURRENCY",TO_DATE(TO_CHAR(INTERN AL_FUNCTION("T_RMB"."ISSUANCEDATE"),'yyyymmdd'),'yyyymmdd') ORDER BY "STATUS")<=1)看到这种不加排序的子句直接使用rownum=1的查询，本身就是对数据要求不严格。所以只要达到它的一个特性（最多返回一行）就可以，上面的标量语句就可以改为：
(select max(s.exechangerate) exechangerate, s.exchangecurrency, TO_CHAR(s.issuancedate, 'yyyymmdd') issuancedate1 from tcsa.uccexchange s group by TO_CHAR(s.issuancedate, 'yyyymmdd'),s.exchangecurrency)整体改写后：
select postdate, CusNo, dramount, Debit_Sum_RMB, cramount, Credit_Sum_RMB from (select to_date(o.postdate, 'yyyymmdd') postdate, cur.c_code as CusNo, o.dramount, round(o.dramount * (s.exechangerate1 / 100), 2) AS Debit_Sum_RMB, o.cramount, round(o.cramount * (s.exechangerate1 / 100), 2) AS Credit_Sum_RMB, s.exchangecurrency, t.t_code from tcsa.mm_sap_voucher_detail_to o left join cur_tbl cur on cur.c_abbr = o.currencycode left join (select max(exechangerate) exechangerate1, exchangecurrency, TO_CHAR(issuancedate, 'yyyymmdd') issuancedate1 from tcsa.uccexchange group by exchangecurrency, TO_CHAR(issuancedate, 'yyyymmdd')) s on s.issuancedate1 = o.postdate left join tcsa.sap_code_mapping t on t.typename = 'currency' and t.sap_code = o.currencycode where o.postdate >= '20170101' and o.postdate < '20180101') tab where t_code = exchangecurrency;转载于:https://www.cnblogs.com/wanbin/p/9514705.html
相关资源：JAVA上百实例源码以及开源项目