行集合に連番を割り当てる
主キーが1つの場合
まずは行に連番を割り振ることから始めましょう。表1のような、
student_ | Weight |
---|---|
A100 | 50 |
A101 | 55 |
A124 | 55 |
B343 | 60 |
B346 | 72 |
C563 | 72 |
C345 | 72 |
学生ID順に連番を振ることを考えます。ROW_
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_
SELECT student_id,
(SELECT COUNT(*)
FROM Weights W2
WHERE W2.student_id <= W1.student_id) AS seq →ROW_NUMBER を相関サブクエリで代用
FROM Weights W1;
主キーが2つ以上の場合
さて、
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 |
まず、
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
一方、
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;
このとき注意が必要なのは、
クラスごとに連番を振る
考え方をちょっと変えて、
SELECT class, student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM Weights2;
SELECT class, student_id,
(SELECT COUNT(*)
FROM Weights2 W2
WHERE W2.class = W1.class
AND W2.student_id <= W1.student_id) AS seq
FROM Weights2 W1;
class studdent_id seq
----- ------------ ----------
1 100 1
1 101 2
1 102 3
----------------------------- ←クラスが変わると連番がリセットされる
2 100 1
2 101 2
2 102 3
2 103 4
連番の列が空の場合
さて、
class (クラス) | student_ (学生ID) | weight (体重kg) | seq(連番) |
---|---|---|---|
1 | 100 | 50 | |
1 | 101 | 55 | |
1 | 102 | 56 | |
2 | 100 | 60 | |
2 | 101 | 72 | |
2 | 102 | 73 | |
2 | 103 | 73 |
基本的には先ほどの連番を振るクエリの結果をSET句に埋め込む、
UPDATE Weights3
SET seq = (SELECT seq
FROM (SELECT class, student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY class, student_id) AS seq
FROM Weights3) SeqTbl
WHERE Weights3.class = SeqTbl.class
AND Weights3.student_id = SeqTbl.student_id);
UPDATE Weights3
SET seq = (SELECT COUNT(*)
FROM Weights3 W2
WHERE W2.class = Weights3.class
AND W2.student_id <= Weights3.student_id);
class (クラス) | student_ (学生ID) | weight (体重kg) | seq(連番) |
---|---|---|---|
1 | 100 | 50 | 1 |
1 | 101 | 55 | 2 |
1 | 102 | 56 | 3 |
2 | 100 | 60 | 1 |
2 | 101 | 72 | 2 |
2 | 102 | 73 | 3 |
2 | 103 | 73 | 4 |
更新対象のテーブルに相関名をつけない
どちらのケースでも注意が必要なのは、