'分析函数' Tag

• 列转换行

  12月 3, 2009

  1、表数据如下： SQL> select * from test; ID MC ---------- ---------- 1 11111 1 22222 2 11111 2 22222 3 11111 3 22222 3 33333 7 rows selected 2、 SQL> select id,mc, 2 row_number() over(partition by id order by id) rn_by_id, 3 dense_rank() over(order by id) as dr, 4 row_number() over(order by id) + dense_rank() over(order [...]

  posted in Oracle 知识杂记 by | 编辑

• 查找重复记录的rowid

  08月 22, 2009

  scott@MYORACLE> select rowid,t.*,row_number() over(partition by deptno order by rowid desc) row_flag from tmp_dept t; ROWID                  DEPTNO DNAME          LOC                    ROW_FLAG ------------------ ---------- -------------- -------------------- ---------- AAAGYyAAIAAAABUAAE         11 123            00123                         1 AAAGYyAAIAAAABUAAF         12 123            00123                         1 AAAGYyAAIAAAABUAAB         20 RESEARCH       DALLAS                        1 AAAGYyAAIAAAABUAAC         30 SALES          CHICAGO                       1 AAAGYyAAIAAAABUAAD         40 OPERATIONS     BOSTON                        1 AAAGYyAAIAAAABUAAJ         70 a              k                             1 AAAGYyAAIAAAABUAAH         [...]

  posted in Oracle 知识杂记 by | 编辑