本日はナンバリングに関して。
MySQLを使っていますと各テーブルにはid int not null auto_increment primary keyなどと打って主キーを打つことがほぼ習慣になっておりましたが、これは比較的新しい考え方のようです。
Contents
主キーが1列の場合
テーブル名member カラムはmember_no,nameだと仮定します。
全てのレコードに対して通し番号での連番を振る場合です。
ウインドウ関数を使う場合
|
1 2 3 4 5 6 |
SELECT ROW_NUMBER ()OVER( ORDER BY member_no ) AS seq, member_no, name FROM member |
サブクエリで通し番号を取る場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT m2.member_no, m2.weight, ( SELECT COUNT(*) FROM member m1 WHERE m1.member_no <= m2.member_no ) as seq_id FROM member m2 ORDER BY m2.member_no |
主キーが複数列から構成されている場合(※通し番号)
テーブルサンプル(score)
class | member_no | point
——-+———–+——-
1 | 100 | 50
1 | 101 | 55
1 | 102 | 56
2 | 100 | 60
2 | 101 | 72
2 | 102 | 73
2 | 103 | 73
ウインドウ関数
|
1 2 3 4 5 6 |
SELECT ROW_NUMBER ()OVER( ORDER BY class,member_no ) AS seq, class, member_no FROM score |
サブクエリ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT s2.class, s2.member_no, ( SELECT COUNT(*) FROM score s1 WHERE (s1.class, s1.member_no) <= (s2.class, s2.member_no) ) as seq_id FROM score s2 ORDER BY seq_id |
グループごとに通し番号
ウィンドウ関数
|
1 2 3 4 5 6 |
SELECT ROW_NUMBER () OVER( PARTITION BY class ORDER BY member_no ) AS seq, class, member_no FROM score |
サブクエリ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT s2.class, s2.member_no, ( SELECT COUNT(*) FROM score s1 WHERE s1.class = s2.class and s1.member_no <= s2.member_no ) as seq_id FROM score s2 ORDER BY s2.class,seq_id |
ちなみにこれらを利用してUPDATE文を作成し、シーケンス的なidを作成することができます。
