確認用データ
以下のデータを使う。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT * FROM subject_scores; +-------------+-------+ | subject | score | +-------------+-------+ | mathematics | 85 | | physics | 95 | | chemistry | 80 | | geology | 70 | | literature | NULL | | philosophy | 80 | +-------------+-------+ 6 rows in set (0.00 sec) |
基本形
FROM句の中にSELECTクエリーを書いてエイリアスをつけると、それがテーブルとして参照される。
|
1 2 3 4 |
SELECT * FROM (SELECT subject, score FROM subject_scores) as view ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT * -> FROM -> (SELECT subject, score FROM subject_scores) as view -> ; +-------------+-------+ | subject | score | +-------------+-------+ | mathematics | 85 | | physics | 95 | | chemistry | 80 | | geology | 70 | | literature | NULL | | philosophy | 80 | +-------------+-------+ 6 rows in set (0.00 sec) |
インラインビューにはエイリアスを付けないとエラーになる。
|
1 2 3 4 5 |
mysql> SELECT * -> FROM -> (SELECT subject, score FROM subject_scores) -> ; ERROR 1248 (42000): Every derived table must have its own alias |
テーブルとインラインビューの併記
エイリアスによる修飾
テーブルとインラインビューを並べて使える。不明確さをなくすために、テーブル名またはエイリアスで列名を修飾する。
|
1 2 3 4 5 |
SELECT org.subject, org.score, view.subject, view.score FROM subject_scores as org, (SELECT subject, score FROM subject_scores) as view ; |
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
mysql> SELECT org.subject, org.score, view.subject, view.score -> FROM -> subject_scores as org, -> (SELECT subject, score FROM subject_scores) as view -> ; +-------------+-------+-------------+-------+ | subject | score | subject | score | +-------------+-------+-------------+-------+ | philosophy | 80 | mathematics | 85 | | literature | NULL | mathematics | 85 | | geology | 70 | mathematics | 85 | | chemistry | 80 | mathematics | 85 | | physics | 95 | mathematics | 85 | | mathematics | 85 | mathematics | 85 | | philosophy | 80 | physics | 95 | | literature | NULL | physics | 95 | | geology | 70 | physics | 95 | | chemistry | 80 | physics | 95 | | physics | 95 | physics | 95 | | mathematics | 85 | physics | 95 | | philosophy | 80 | chemistry | 80 | | literature | NULL | chemistry | 80 | | geology | 70 | chemistry | 80 | | chemistry | 80 | chemistry | 80 | | physics | 95 | chemistry | 80 | | mathematics | 85 | chemistry | 80 | | philosophy | 80 | geology | 70 | | literature | NULL | geology | 70 | | geology | 70 | geology | 70 | | chemistry | 80 | geology | 70 | | physics | 95 | geology | 70 | | mathematics | 85 | geology | 70 | | philosophy | 80 | literature | NULL | | literature | NULL | literature | NULL | | geology | 70 | literature | NULL | | chemistry | 80 | literature | NULL | | physics | 95 | literature | NULL | | mathematics | 85 | literature | NULL | | philosophy | 80 | philosophy | 80 | | literature | NULL | philosophy | 80 | | geology | 70 | philosophy | 80 | | chemistry | 80 | philosophy | 80 | | physics | 95 | philosophy | 80 | | mathematics | 85 | philosophy | 80 | +-------------+-------+-------------+-------+ 36 rows in set (0.00 sec) |
列名のエイリアス
インラインビューの列名にエイリアスを定義してもよい。
|
1 2 3 4 5 |
SELECT subject, score, view_subject, view_score FROM subject_scores, (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT subject, score, view_subject, view_score -> FROM -> subject_scores, -> (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view -> ; +-------------+-------+--------------+------------+ | subject | score | view_subject | view_score | +-------------+-------+--------------+------------+ | philosophy | 80 | mathematics | 85 | | literature | NULL | mathematics | 85 | | geology | 70 | mathematics | 85 | | chemistry | 80 | mathematics | 85 | | physics | 95 | mathematics | 85 | | mathematics | 85 | mathematics | 85 | | philosophy | 80 | physics | 95 | | literature | NULL | physics | 95 | ..... |
集約関数の利用
インラインビューで集約関数を使うと威力を発揮。以下の例では、全レコード中の最高得点と最低得点を各レコードに付加している。
|
1 2 3 4 5 6 |
SELECT subject, score, max_score, min_score FROM subject_scores, (SELECT MAX(score) as max_score FROM subject_scores) as max_table, (SELECT MIN(score) as min_score FROM subject_scores) as min_table ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT subject, score, max_score, min_score -> FROM -> subject_scores, -> (SELECT MAX(score) as max_score FROM subject_scores) as max_table, -> (SELECT MIN(score) as min_score FROM subject_scores) as min_table -> ; +-------------+-------+-----------+-----------+ | subject | score | max_score | min_score | +-------------+-------+-----------+-----------+ | mathematics | 85 | 95 | 70 | | physics | 95 | 95 | 70 | | chemistry | 80 | 95 | 70 | | geology | 70 | 95 | 70 | | literature | NULL | 95 | 70 | | philosophy | 80 | 95 | 70 | +-------------+-------+-----------+-----------+ 6 rows in set (0.00 sec) |
このようにすると、最高得点や最低得点のレコードを抽出することができる。
|
1 2 3 4 5 6 7 8 |
SELECT subject, score, max_score, min_score FROM subject_scores, (SELECT MAX(score) as max_score FROM subject_scores) as max_table, (SELECT MIN(score) as min_score FROM subject_scores) as min_table WHERE score = max_score ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SELECT subject, score, max_score, min_score -> FROM -> subject_scores, -> (SELECT MAX(score) as max_score FROM subject_scores) as max_table, -> (SELECT MIN(score) as min_score FROM subject_scores) as min_table -> WHERE -> score = max_score -> ; +---------+-------+-----------+-----------+ | subject | score | max_score | min_score | +---------+-------+-----------+-----------+ | physics | 95 | 95 | 70 | +---------+-------+-----------+-----------+ 1 row in set (0.00 sec) |