行集合に連番を割り当てる
主キーが1つの場合
まずは行に連番を割り振ることから始めましょう。表1のような，学生の体重を保持する簡単なテーブルを例に使います。
表1 Weights
| student_id（学生ID） | Weight（体重kg） |
|---|---|
| A100 | 50 |
| A101 | 55 |
| A124 | 55 |
| B343 | 60 |
| B346 | 72 |
| C563 | 72 |
| C345 | 72 |
学生ID順に連番を振ることを考えます。ROW_NUMBER関数が使える環境なら，リスト1のように簡単に記述でき，図1の実行結果が得られます。
リスト1 主キーが1列の場合(ROW_NUMBER)
SELECT student_id,
ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;
図1 リスト1，2の実行結果
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つずつ増えていくことが保証されます。それを連番の生成に利用しているのがこのトリックの要諦です。
リスト2 主キーが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」で一意になります。こういうケースでも連番を割り振れるよう一般化しましょう。
表2 Weights2
| class(クラス) | student_id(学生ID) | weight(体重kg) |
|---|---|---|
| 1 | 100 | 50 |
| 1 | 101 | 55 |
| 1 | 102 | 56 |
| 2 | 100 | 60 |
| 2 | 101 | 72 |
| 2 | 102 | 73 |
| 2 | 103 | 73 |
まず，ROW_NUMBERを使う場合には，特に悩む必要はありません。ORDER BYのキーに追加しましょう（リスト3）。実行結果は図2です。
リスト3 主キーが複数列の場合(ROW_NUMBER)
SELECT class, student_id,
ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;
図2 リスト3，4の実行結果
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）。
リスト4 主キーが複数列の場合（相関サブクエリ：文字列結合して1 列とみなす）
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列以上になってもまったく同じように拡張できます。
