概要
インラインビューとグルーピングを使って、レコード中のある列の値が最大値／最小値となるレコードをグループごとに抽出できる。
以下、実行例に沿ってその方法を整理する。
実行例
以下のデータを使う。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT * FROM student_scores; +------+---------+-------+ | name | subject | score | +------+---------+-------+ | 安藤 | 数学 | 80 | | 安藤 | 物理 | 90 | | 安藤 | 英語 | 75 | | 伊藤 | 数学 | 65 | | 伊藤 | 文学 | 85 | | 宇藤 | 物理 | 70 | | 宇藤 | 英語 | 85 | | 宇藤 | 文学 | 95 | | 江藤 | 数学 | 85 | | 江藤 | 物理 | 80 | | 江藤 | 文学 | 65 | +------+---------+-------+ 11 rows in set (0.00 sec) |
ここでは各科目ごとの最高点をとった学生と教科を抽出する。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT name, subject, score, max_sub, max_scr FROM student_scores, ( SELECT subject AS max_sub, MAX(score) AS max_scr FROM student_scores GROUP BY subject ) AS max_table WHERE subject = max_sub AND score = max_scr ; |
|
1 2 3 4 5 6 7 8 9 |
+------+---------+-------+---------+---------+ | name | subject | score | max_sub | max_scr | +------+---------+-------+---------+---------+ | 安藤 | 物理 | 90 | 物理 | 90 | | 宇藤 | 英語 | 85 | 英語 | 85 | | 宇藤 | 文学 | 95 | 文学 | 95 | | 江藤 | 数学 | 85 | 数学 | 85 | +------+---------+-------+---------+---------+ 4 rows in set (0.00 sec) |
手順
グループごとの最大値
科目によってグルーピングし、各科目の最高得点を得る。
|
1 |
SELECT subject, MAX(score) FROM student_scores GROUP BY subject; |
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject; +---------+------------+ | subject | MAX(score) | +---------+------------+ | 数学 | 85 | | 物理 | 90 | | 英語 | 85 | | 文学 | 95 | +---------+------------+ 4 rows in set (0.00 sec) |
インラインビューによる最高得点列の追加
インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最高スコアの列が追加される。
|
1 2 3 4 5 6 7 8 9 |
SELECT name, subject, max_sub, max_scr FROM student_scores, ( SELECT subject AS max_sub, MAX(score) AS max_scr FROM student_scores GROUP BY subject ) AS max_table ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> SELECT name, subject, max_sub, max_scr -> FROM -> student_scores, -> ( -> SELECT subject AS max_sub, MAX(score) AS max_scr -> FROM student_scores -> GROUP BY subject -> ) AS max_table -> ; +------+---------+---------+---------+ | name | subject | max_sub | max_scr | +------+---------+---------+---------+ | 江藤 | 文学 | 数学 | 85 | | 江藤 | 物理 | 数学 | 85 | | 江藤 | 数学 | 数学 | 85 | | 宇藤 | 文学 | 数学 | 85 | | 宇藤 | 英語 | 数学 | 85 | | 宇藤 | 物理 | 数学 | 85 | | 伊藤 | 文学 | 数学 | 85 | | 伊藤 | 数学 | 数学 | 85 | | 安藤 | 英語 | 数学 | 85 | | 安藤 | 物理 | 数学 | 85 | | 安藤 | 数学 | 数学 | 85 | | 江藤 | 文学 | 物理 | 90 | | 江藤 | 物理 | 物理 | 90 | | 江藤 | 数学 | 物理 | 90 | | 宇藤 | 文学 | 物理 | 90 | ..... |
最高得点のレコードの追加
各学生・科目のレコードのうち、スコアが最高スコアに等しいレコードを抽出する。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT name, subject, score, max_sub, max_scr FROM student_scores, ( SELECT subject AS max_sub, MAX(score) AS max_scr FROM student_scores GROUP BY subject ) AS max_table WHERE subject = max_sub AND score = max_scr ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SELECT name, subject, score, max_sub, max_scr -> FROM -> student_scores, -> ( -> SELECT subject AS max_sub, MAX(score) AS max_scr -> FROM student_scores -> GROUP BY subject -> ) AS max_table -> WHERE subject = max_sub AND score = max_scr -> ; +------+---------+-------+---------+---------+ | name | subject | score | max_sub | max_scr | +------+---------+-------+---------+---------+ | 安藤 | 物理 | 90 | 物理 | 90 | | 宇藤 | 英語 | 85 | 英語 | 85 | | 宇藤 | 文学 | 95 | 文学 | 95 | | 江藤 | 数学 | 85 | 数学 | 85 | +------+---------+-------+---------+---------+ 4 rows in set (0.00 sec) |