10-70 条件を満たす行に連番を設定

SQLパズル

test_table
 ENAME   SAL  DEPTNO  RANK_SAL
------  ----  ------  --------
MILLER  1300      10      null
CLARK   2450      10      null
KING    5000      10      null
SMITH    800      20      null
ADAMS   1100      20      null
JONES   2975      20      null
FORD    3000      20      null
SCOTT   3000      20      null
JAMES    950      30      null
MARTIN  1250      30      null
WARD    1250      30      null
TURNER  1500      30      null
ALLEN   1600      30      null
BLAKE   2850      30      null

DEPTNOが20以上のレコードに、
DEPTNOごとの、
SALの昇順に連番をセットする(SALが等しい場合は、ENAMEを比較)

更新結果
 ENAME   SAL  DEPTNO  RANK_SAL
------  ----  ------  --------
MILLER  1300      10      null
CLARK   2450      10      null
KING    5000      10      null
SMITH    800      20         1
ADAMS   1100      20         2
JONES   2975      20         3
FORD    3000      20         4
SCOTT   3000      20         5
JAMES    950      30         1
MARTIN  1250      30         2
WARD    1250      30         3
TURNER  1500      30         4
ALLEN   1600      30         5
BLAKE   2850      30         6

データ作成スクリプト

create table test_table as
select ENAME,SAL,DEPTNO,cast(null as number(1)) as RANK_SAL from scott.emp;

SQL

--■■■分析関数を使う方法■■■
update test_table a
set rank_sal = (select b.Rank
                  from (select c.RowID as Row_ID,
                        Row_Number() over(partition by c.DEPTNO order by c.SAL,c.ENAME) as Rank
                          from test_table c
                         where c.DEPTNO >=20) b
                 where b.Row_ID = a.RowID)
where DEPTNO >=20;

--■■■相関サブクエリを使う方法■■■
update test_table a
set rank_sal = (select count(*)+1 from test_table b
                 where b.DEPTNO = a.DEPTNO
                   and (b.SAL < a.SAL
                     or b.SAL = a.SAL and b.ENAME < a.ENAME))
where DEPTNO >=20;

解説

分析関数を使う方法では、
RowIDを使って結合させてます

---