行集合に連番を割り当てる
主キーが1つの場合
まずは行に連番を割り振ることから始めましょう。表1のような,
表1 Weights
student_ | Weight |
---|---|
A100 | 50 |
A101 | 55 |
A124 | 55 |
B343 | 60 |
B346 | 72 |
C563 | 72 |
C345 | 72 |
学生ID順に連番を振ることを考えます。ROW_
リスト1 主キーが1列の場合(ROW_
SELECT student_id,
ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;
図1 リスト1,
student_id seq ----------- ---- A100 1 A101 2 A124 3 B343 4 B346 5 C345 6 C563 7
PostgreSQLやMySQLといったROW_
リスト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 Weights2
class(クラス) | student_ | weight(体重kg) |
---|---|---|
1 | 100 | 50 |
1 | 101 | 55 |
1 | 102 | 56 |
2 | 100 | 60 |
2 | 101 | 72 |
2 | 102 | 73 |
2 | 103 | 73 |
まず,
リスト3 主キーが複数列の場合(ROW_
SELECT class, student_id,
ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;
図2 リスト3,
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
一方,
リスト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;
このとき注意が必要なのは,