利用分析函数改写解析

来源:互联网 发布:现在民办学校域名注册 编辑:程序博客网 时间:2024/05/15 02:53
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
原创粉丝点击