利用分析函数改写解析
来源：互联网 发布：现在民办学校域名注册 编辑：程序博客网 时间：2024/05/29 08:39
SQL> SELECT acct_no, trans_amt, set_date, opp_acct_no, dc_flag, seqno, MAX(seqno) over(PARTITION BY acct_no, trans_amt, set_date, opp_acct_no, dc_flag) max_seq FROM t100 where trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' 2 3 4 5 6 7 8 9 10 11 12 13 ;ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NO DC_FLAG SEQNOMAX_SEQ------------------------------ ---------- ------------ -------------------------------- - ---------- ----------12601002510090000222 5890 21-FEB-141 3 312601002510090000222 5890 21-FEB-141 2 712601002510090000222 5890 21-FEB-141 5 712601002510090000222 5890 21-FEB-141 6 712601002510090000222 5890 21-FEB-141 7 712601002510090000222 5890 21-FEB-141 4 715604012110300062424 5890 21-FEB-141 2 615604012110300062424 5890 21-FEB-141 2 615604012110300062424 5890 21-FEB-141 6 615604012110300062424 5890 21-FEB-141 5 615604012110300062424 5890 21-FEB-141 4 615604012110300062424 5890 21-FEB-141 3 615801012110300054084 5890 21-FEB-141 2 215801012110300083257 5890 21-FEB-141 2 215901012110300090409 5890 21-FEB-141 1 115901012110300090664 5890 21-FEB-141 1 116101012110300000602 5890 21-FEB-141 2 216101012110300020444 5890 21-FEB-141 2 216601012110300088200 5890 21-FEB-141 2 216601012110300108974 5890 21-FEB-141 2 218801012110300059452 5890 21-FEB-141 1 118801012110300059668 5890 21-FEB-141 1 1018801012110300059668 5890 21-FEB-141 10 1019601012110300054845 5890 21-FEB-141 2 235601012110300046163 5890 21-FEB-141 2 235801012110300006569 5890 21-FEB-141 2 256567012110300000936 5890 21-FEB-141 2 256567012110300007824 5890 21-FEB-141 2 228 rows selected.按ACCT_NO,TRANS_AMT,SET_DATE,OPP_ACCT_NO,DC_FLAG 汇总求最大值SQL> col acct_no format a30SQL> SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno FROM t100 where trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1'and seqno = ( SELECT MAX(seqno) FROM t100where trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1'); 2 3 4 5 6 7 8 9 10 11 12 13 14 ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNO------------------------------ ---------- ------------ -------------------------------- - ----------18801012110300059668 5890 21-FEB-141 10SQL> WITH A AS (SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno,MAX(seqno) OVER (PARTITION BY acct_no, trans_amt ,set_date,opp_acct_no ,dc_flag) max_seq FROM t100 )SELECT * FROM a WHERE trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' and a.seqno=a.max_seq 2 3 4 5 6 7 8 9 10 ;ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNOMAX_SEQ------------------------------ ---------- ------------ -------------------------------- - ---------- ----------18801012110300059668 5890 21-FEB-141 10 1018801012110300059668 5890 21-FEB-141 1 1SQL> WITH A AS (SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno,MAX(seqno) OVER (PARTITION BY acct_no, trans_amt ,set_date,opp_acct_no ,dc_flag) max_seq FROM t100 )SELECT * FROM a WHERE trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' and a.seqno=a.max_seq 2 3 4 5 6 7 8 9 10 ;Execution Plan----------------------------------------------------------Plan hash value: 427747411----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 137 | 420 (1)| 00:00:06 ||* 1 | VIEW | | 1 | 137 | 420 (1)| 00:00:06 || 2 | WINDOW SORT | | 1 | 124 | 420 (1)| 00:00:06 ||* 3 | TABLE ACCESS FULL| T100 | 1 | 124 | 419 (1)| 00:00:06 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"."SEQNO"="A"."MAX_SEQ") 3 - filter("OPP_ACCT_NO" IS NULL AND "TRANS_AMT"=5890 AND "SET_DATE"=TO_DATE(' 2014-02-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DC_FLAG"='1' AND TRIM("ACCT_NO")='18801012110300059668')Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 1 db block gets 1520 consistent gets 0 physical reads 0 redo size 1223 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed这样写等价:SQL> col acct_no format a30SQL> WITH A AS (SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno,MAX(seqno) OVER (PARTITION BY trim(acct_no), trans_amt ,set_date,opp_acct_no ,dc_flag) max_seq FROM t100 )SELECT * FROM a WHERE trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' and a.seqno=a.max_seq 2 3 4 5 6 7 8 9 10 ;ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNOMAX_SEQ------------------------------ ---------- ------------ -------------------------------- - ---------- ----------18801012110300059668 5890 21-FEB-141 10 10继续测试:SQL> SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno FROM t10 where trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1'and seqno = ( SELECT MAX(seqno) FROM t10where trim(acct_no)='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1'); 2 3 4 5 6 7 8 9 10 11 12 13 14 ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNO------------------------------ ---------- ------------ -------------------------------- - ----------18801012110300059668 5890 21-FEB-141 10SQL> WITH A AS (SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno,MAX(seqno) OVER (PARTITION BY acct_no, trans_amt ,set_date,opp_acct_no ,dc_flag) max_seq FROM t10 )SELECT * FROM a WHERE acct_no='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' and a.seqno=a.max_seq 2 3 4 5 6 7 8 9 10 ;ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNOMAX_SEQ------------------------------ ---------- ------------ -------------------------------- - ---------- ----------18801012110300059668 5890 21-FEB-141 10 10SQL> WITH A AS (SELECT acct_no,trans_amt , set_date,opp_acct_no ,dc_flag,seqno,MAX(seqno) OVER (PARTITION BY acct_no, trans_amt ,set_date,opp_acct_no ,dc_flag) max_seq FROM t10 )SELECT * FROM a WHERE acct_no='18801012110300059668' AND trans_amt ='5890.00' AND set_date=DATE '2014-2-21' AND opp_acct_no IS NULL AND dc_flag='1' 2 3 4 5 6 7 8 9 ;ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NOD SEQNOMAX_SEQ------------------------------ ---------- ------------ -------------------------------- - ---------- ----------18801012110300059668 5890 21-FEB-141 10 1018801012110300059668 5890 21-FEB-141 1 10 0 0
- 利用分析函数改写解析
- 数组越界溢出利用--改写对象虚函数表
- 使用分析函数改写cube，使其一步满足需求
- 利用JSON解析函数
- gsl_ran_gaussian函数改写
- 如何利用网页改写注册表
- 利用perl 改写awk统计
- 利用WITH AS改写SQL
- 利用JDOM解析xml步骤实例分析
- 利用aio+epoll简单改写sendfile
- Oracle 分析函数及开窗函数 解析
- 改写版的tinyxml，HTML解析器
- 利用分析函数优化自连接
- 利用分析函数优化自连接
- 利用分析函数删除重复数据
- gcc源代码分析，debug_tree()函数的利用
- 利用分析函数生成连续数据
- mysql利用自定义变量实现分析函数
- 反渗透设备：影响反渗透系统的各种离子因素
- Vim简明教程【CoolShell】
- 程序员练级之路 （作者：陈皓）
- OpenCV编程->CvvImage源码及使用
- 关于Socket和NetWorkstream介绍
- 利用分析函数改写解析
- SQLSERVER中统计所有表的记录数
- 反渗透设备：RO反渗透设备制取离子水的原理
- N版耳机与I版耳机的改造
- 给UIImageView添加点击事件
- IIS7下打开文件出现 403 - 禁止访问：访问被拒绝
- interview 01
- 【转】Hadoop-2.5.1集群安装配置笔记
- 第四章《字符串和多维数组》总结
