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 SEQNO MAX_SEQ
------------------------------ ---------- ------------ -------------------------------- - ---------- ----------
12601002510090000222 5890 21-FEB-14 1 3 3
12601002510090000222 5890 21-FEB-14 1 2 7
12601002510090000222 5890 21-FEB-14 1 5 7
12601002510090000222 5890 21-FEB-14 1 6 7
12601002510090000222 5890 21-FEB-14 1 7 7
12601002510090000222 5890 21-FEB-14 1 4 7
15604012110300062424 5890 21-FEB-14 1 2 6
15604012110300062424 5890 21-FEB-14 1 2 6
15604012110300062424 5890 21-FEB-14 1 6 6
15604012110300062424 5890 21-FEB-14 1 5 6
15604012110300062424 5890 21-FEB-14 1 4 6
15604012110300062424 5890 21-FEB-14 1 3 6
15801012110300054084 5890 21-FEB-14 1 2 2
15801012110300083257 5890 21-FEB-14 1 2 2
15901012110300090409 5890 21-FEB-14 1 1 1
15901012110300090664 5890 21-FEB-14 1 1 1
16101012110300000602 5890 21-FEB-14 1 2 2
16101012110300020444 5890 21-FEB-14 1 2 2
16601012110300088200 5890 21-FEB-14 1 2 2
16601012110300108974 5890 21-FEB-14 1 2 2
18801012110300059452 5890 21-FEB-14 1 1 1
18801012110300059668 5890 21-FEB-14 1 1 10
18801012110300059668 5890 21-FEB-14 1 10 10
19601012110300054845 5890 21-FEB-14 1 2 2
35601012110300046163 5890 21-FEB-14 1 2 2
35801012110300006569 5890 21-FEB-14 1 2 2
56567012110300000936 5890 21-FEB-14 1 2 2
56567012110300007824 5890 21-FEB-14 1 2 2
28 rows selected.
按ACCT_NO,TRANS_AMT,SET_DATE,OPP_ACCT_NO,DC_FLAG 汇总求最大值
SQL> col acct_no format a30
SQL> 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 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'); 2 3 4 5 6 7 8 9 10 11 12 13 14
ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NO D SEQNO
------------------------------ ---------- ------------ -------------------------------- - ----------
18801012110300059668 5890 21-FEB-14 1 10
SQL> 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_NO D SEQNO MAX_SEQ
------------------------------ ---------- ------------ -------------------------------- - ---------- ----------
18801012110300059668 5890 21-FEB-14 1 10 10
18801012110300059668 5890 21-FEB-14 1 1 1
SQL> 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 client
520 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 a30
SQL> 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_NO D SEQNO MAX_SEQ
------------------------------ ---------- ------------ -------------------------------- - ---------- ----------
18801012110300059668 5890 21-FEB-14 1 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 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');
2 3 4 5 6 7 8 9 10 11 12 13 14
ACCT_NO TRANS_AMT SET_DATE OPP_ACCT_NO D SEQNO
------------------------------ ---------- ------------ -------------------------------- - ----------
18801012110300059668 5890 21-FEB-14 1 10
SQL> 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_NO D SEQNO MAX_SEQ
------------------------------ ---------- ------------ -------------------------------- - ---------- ----------
18801012110300059668 5890 21-FEB-14 1 10 10
SQL> 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_NO D SEQNO MAX_SEQ
------------------------------ ---------- ------------ -------------------------------- - ---------- ----------
18801012110300059668 5890 21-FEB-14 1 10 10
18801012110300059668 5890 21-FEB-14 1 1 10