行集合に連番を割り当てる

主キーが1つの場合

まずは行に連番を割り振ることから始めましょう。表1のような，学生の体重を保持する簡単なテーブルを例に使います。

学生ID順に連番を振ることを考えます。ROW_NUMBER関数が使える環境なら，リスト1のように簡単に記述でき，図1の実行結果が得られます。

SELECT student_id,
       ROW_NUMBER() OVER (ORDER BY student_id) AS seq
    FROM Weights;

student_id   seq
-----------   ----
A100           1
A101           2
A124           3
B343           4
B346           5
C345           6
C563           7

PostgreSQLやMySQLといったROW_NUMBER関数が使えない実装なら，相関サブクエリで置き換えましょう（リスト2⁠）⁠。このサブクエリは，ノイマン型の再帰集合を作り，その要素数をCOUNT関数で数えているのです（注2⁠）⁠。主キーであるsutdent_idを比較キーに使用しているため，再帰集合は要素が1つずつ増えていくことが保証されます。それを連番の生成に利用しているのがこのトリックの要諦です。

SELECT student_id,
       (SELECT COUNT(*)
          FROM Weights W2
         WHERE W2.student_id <= W1.student_id) AS seq →ROW_NUMBER を相関サブクエリで代用
  FROM Weights W1;

注2）

gihyo.jp連載「SQLアタマ養成講座」の第5回「SQL流行間比較（1）はじめに」を参照。再帰集合のイメージは，入れ子集合の図を見るとわかりやすいでしょう。

主キーが2つ以上の場合

さて，それではちょっとテーブルを改変して，主キーを2つの複合キーにしてみましょう（表2⁠）⁠。今度は「クラス，学生ID」で一意になります。こういうケースでも連番を割り振れるよう一般化しましょう。

まず，ROW_NUMBERを使う場合には，特に悩む必要はありません。ORDER BYのキーに追加しましょう（リスト3⁠）⁠。実行結果は図2です。

SELECT class, student_id,
       ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
  FROM Weights2;

class  student_id seq
----- ------------ ---
    1  100         1
    1  101         2
    1  102         3
    2  100         4
    2  101         5
    2  102         6
    2  103         7

一方，相関サブクエリの場合はどうでしょう。いくつか方法があるのですが，私が最もシンプルと思うのは，複合キーを文字列とみなして連結し，強引に1列のキーに作り変えてしまうことです。そうすれば，あとは先ほどと原理は同じです（リスト4⁠）⁠。

SELECT class, student_id,
       (SELECT COUNT(*)
          FROM Weights2 W2
         WHERE (W2.class || W2.student_id) <= (W1.class || W1.student_id) ) AS seq
  FROM Weights2 W1;

このとき注意が必要なのは，キーが数値や日付など文字型以外のデータ型の場合は，結合の前に文字型へのキャストが必要なことです。このときにデータの桁数や書式を統一しておくことが無用の混乱を避けるうえでも重要です（特に数値の場合は，きっちり前ゼロを埋める形式にしないとうまく連番が並びません⁠）⁠。あとは，キーが3列以上になってもまったく同じように拡張できます。