SELECT prim_key,prim_key1 FROM (SELECT prim_key,field1,lead(field1,1)over(PARTITION BY 1 ORDER BY prim_key) rn, lead(prim_key,1)over(PARTITION BY 1 ORDER BY prim_key) prim_key1 FROM test_20130114) WHERE rn=field1;可能比较恶心,你慢慢看。表数据如下 SQL> select * from test_20130114; PRIM_KEY FIELD1 ---------- ---------- 1 200110 2 200111 3 200110 4 200110
SQL> select * from tt;
ID COL1 COL2 COL3 ---------- ---------- -------------------- ---------- 1 200110 a 2 200111 b 3 200110 c 4 200110 d 5 200111 e 6 200110 f
6 rows selected
SQL> SQL> select id, col1, col2, col3 2 from (select id, 3 col1, 4 col2, 5 col3, 6 count(1) over(partition by rm, col1) cnt 7 from (select tt.*, 8 id - rank() over(partition by col1 order by id asc) rm 9 from tt)) 10 where cnt > 1;
ID COL1 COL2 COL3 ---------- ---------- -------------------- ---------- 3 200110 c 4 200110 d
SELECT prim_key,prim_key1
FROM
(SELECT prim_key,field1,lead(field1,1)over(PARTITION BY 1 ORDER BY prim_key) rn,
lead(prim_key,1)over(PARTITION BY 1 ORDER BY prim_key) prim_key1 FROM test_20130114)
WHERE rn=field1;可能比较恶心,你慢慢看。表数据如下
SQL> select * from test_20130114; PRIM_KEY FIELD1
---------- ----------
1 200110
2 200111
3 200110
4 200110
SQL> select * from tt;
ID COL1 COL2 COL3
---------- ---------- -------------------- ----------
1 200110 a
2 200111 b
3 200110 c
4 200110 d
5 200111 e
6 200110 f
6 rows selected
SQL>
SQL> select id, col1, col2, col3
2 from (select id,
3 col1,
4 col2,
5 col3,
6 count(1) over(partition by rm, col1) cnt
7 from (select tt.*,
8 id - rank() over(partition by col1 order by id asc) rm
9 from tt))
10 where cnt > 1;
ID COL1 COL2 COL3
---------- ---------- -------------------- ----------
3 200110 c
4 200110 d
SQL>