SQLで分析関数を使わずに簡単に連番を振る方法

例題

このようなテーブルに対して連番を振る

氏名	教科	点数
佐藤	国語	0
佐藤	数学	31
佐藤	英語	19
鈴木	国語	59
鈴木	数学	38
鈴木	英語	25
高橋	国語	92
高橋	数学	100
高橋	英語	39
田中	国語	40
田中	数学	52
田中	英語	30

スキーマ

CREATE TABLE 成績テーブル(氏名 varchar(4), 教科 varchar(4), 点数 int);
INSERT INTO 成績テーブル(氏名, 教科, 点数)
VALUES
    ('佐藤', '国語', 0 ),
    ('佐藤', '数学', 31),
    ('佐藤', '英語', 19),
    ('鈴木', '国語', 59),
    ('鈴木', '数学', 38),
    ('鈴木', '英語', 25),
    ('高橋', '国語', 92),
    ('高橋', '数学', 100),
    ('高橋', '英語', 39),
    ('田中', '国語', 40),
    ('田中', '数学', 52),
    ('田中', '英語', 30);

全体に連番を振る場合

SELECT *
     , (SELECT COUNT(*) 
          FROM 成績テーブル 
         WHERE (氏名, 教科, 点数) <= (Z.氏名, Z.教科, Z.点数)
        ) 連番
 FROM 成績テーブル Z
ORDER BY 1,2,3;

実行結果

氏名	教科	点数	連番
佐藤	国語	0	1
佐藤	数学	31	2
佐藤	英語	19	3
田中	国語	40	4
田中	数学	52	5
田中	英語	30	6
鈴木	国語	59	7
鈴木	数学	38	8
鈴木	英語	25	9
高橋	国語	92	10
高橋	数学	100	11
高橋	英語	39	12

特定のキー毎に連番を振る場合

SELECT *
     , (SELECT COUNT(*) 
          FROM 成績テーブル 
         WHERE (氏名, 教科, 点数) <= (Z.氏名, Z.教科, Z.点数)
           AND (氏名) = (Z.氏名)  -- 複数カラムの時は(列1,列2) = (Z.列1, Z.列2) とできる
        ) 連番
 FROM 成績テーブル Z
ORDER BY 1,2,3;

実行結果

氏名	教科	点数	連番
佐藤	国語	0	1
佐藤	数学	31	2
佐藤	英語	19	3
田中	国語	40	1
田中	数学	52	2
田中	英語	30	3
鈴木	国語	59	1
鈴木	数学	38	2
鈴木	英語	25	3
高橋	国語	92	1
高橋	数学	100	2
高橋	英語	39	3

解説

この記事で強調したかった部分はここです。

WHERE (氏名, 教科, 点数) <= (Z.氏名, Z.教科, Z.点数)
  AND (氏名) = (Z.氏名)  -- 複数カラムの時は(列1,列2) = (Z.列1, Z.列2) とできる

多くのSQLでは、複数のカラムに対して比較演算子が使えます。

お試し