● 2_04_11-検索処理（副問い合わせ-2 相関副問い合わせ）（select）
今まで使っていた非常に単純なテーブルもそろそろ限界なので下記のテーブル、データを使います。
内容は全てダミーです。結合処理に近づいてきたので3つのテーブルを作ります。
以下のSQLを実行してテーブルを作って下さい。
| Create Table e_shouhin_t(
e_shouhin_id VARCHAR(20) NOT NULL -- 商品ID ,e_shouhin_nmae VARCHAR(200) NOT NULL -- 商品名 ,e_shouhin_color VARCHAR(20) -- 色 ,e_shouhin_size VARCHAR(10) -- サイズ ,e_shouhin_kakaku int NOT NULL -- 価格 ,e_shouhin_group VARCHAR(10) NOT NULL -- 商品グループ ,e_shouhin_date timestamp NOT NULL -- データ入力日 ,PRIMARY KEY ( e_shouhin_id ) |
・顧客テーブル
| Create Table e_kokyaku_t(
e_kokyaku_id VARCHAR(200) NOT NULL -- 顧客ID ,e_kokyaku_name VARCHAR(200) NOT NULL -- 顧客名 ,e_kokyaku_adress_1 VARCHAR(100) NOT NULL -- 住所・都道府県 ,e_kokyaku_adress_2 VARCHAR(250) NOT NULL -- 住所・都道府県以外 ,e_kokyaku_tel VARCHAR(40) NOT NULL -- 電話番号 ,e_kokyaku_keitai VARCHAR(40) -- 携帯番号 ,e_kokyaku_fax VARCHAR(40) -- ファックス ,e_kokyaku_mail VARCHAR(200) -- メール・アドレス ,e_kokyaku_shokugyou VARCHAR(30) NOT NULL -- 職業 ,e_kokyaku_company VARCHAR(200) NOT NULL -- 勤め先名 ,e_kokyaku_c_adress_1 VARCHAR(100) -- 勤め先住所・都道府県 ,e_kokyaku_c_adress_2 VARCHAR(250) -- 勤め先住所・都道府県以外 ,e_kokyaku_marry VARCHAR(10) NOT NULL -- 独身・既婚のいずれか ,e_kokyaku_touroku timestamp NOT NULL -- 登録日 ,PRIMARY KEY ( e_kokyaku_id ) |
・販売履歴テーブル
| Create Table e_hanbai_t(
e_hanbai_id serial NOT NULL -- 販売履歴ID ,e_shouhin_id VARCHAR(20) NOT NULL -- 商品ID ,e_kokyaku_id VARCHAR(200) NOT NULL -- 顧客ID ,e_hanbai_date timestamp NOT NULL -- 販売日 ,PRIMARY KEY ( e_hanbai_id ) |
以下のデータをダウンロードして下さい。
e_shouhin_t.lzh
ダウンロードしたら「e_shouhin_t」テーブルにインポートして下さい。
手順は今までで説明しているはずです、忘れたら前に戻って下さい。
e_kokyaku_t.lzh
上のデータはダウンロードしたら「e_kokyaku_t」テーブルにインポートして下さい。
e_hanbai_t.lzh
上のデータはダウンロードしたら「e_hanbai_t」テーブルにインポートして下さい。
副問い合わせのSQLからややこしくなってきます。検索、SELECT文はSQLの中でも一番複雑ですがこれができないと集計などが取れないので頑張って下さい。
書籍やマニュアルですと非常に難解ですのでくだいた説明をします。
ここにある商品があります。
商品ID、 商品名、 色、 サイズ、 価格、 商品グループ、 データ入力日、と項目があります。
商品グループは簡単に把握できるように「シャツ」、「Tシャツ」の2つのグループがあります。
ここでリストを出すように上から言われました。内容は、
1.シャツの平均価格よりも高い商品を、シャツの一覧で出してくれ。
つまりここではTシャツの価格は対象外です。
2.Tシャツの平均価格よりも高い商品を、Tシャツの一覧で出してくれ。
つまりここではシャツの価格は対象外です。
前に説明した副問い合わせではこの処理は無理です。
1つ目のSELECT文で一つのデータを取得してから、2つ目のSELECT文で結果を出していましたから。
もし副問い合わせを使わないなら3回はSQLを発行しなければなりません。
実際にSQLを組むよりもこの理屈の方が大事です。
今回の相関副問い合わせでは「e_shouhin_t」テーブルのみ使います。他のテーブルは後の演習で使います。
急ですが、以下のSQLです、またややこしくなってきました
SELECT a.e_shouhin_name, -- 商品名 a.e_shouhin_color, -- 色 a.e_shouhin_size, -- サイズ a.e_shouhin_kakaku, -- 価格 a.e_shouhin_group -- 商品グループ FROM e_shouhin_t a ←1 WHERE a.e_shouhin_kakaku > ( select avg ( b.e_shouhin_kakaku ) from e_shouhin_t b where b.e_shouhin_group = a.e_shouhin_group ) ←3 order by a.e_shouhin_id |
←1を見て下さい。
「e_shouhin_t a」の部分があります。
←3を見て下さい。
「e_shouhin_t b」の部分があります。
これは「e_shouhin_t」テーブルに対して「a」と「b」という別名を付けて、まったく同じでありながら別物として扱えるようにしています。
これは「b.e_shouhin_group
= a.e_shouhin_group」という扱いを可能にするためです。別物にしないと「=」が成り立ちませんから。
「b.e_shouhin_group」は「e_shouhin_t
b」テーブルのカラム「e_shouhin_group」という意味です。
←2の行の「a.e_shouhin_id」はテーブル「e_shouhin_t
a」のカラム「e_shouhin_id」であるという意味です。
この使い方は後の「テーブル結合」で出てきますから慣れておいて下さい。
( select avg ( b.e_shouhin_kakaku ) from e_shouhin_t
b where b.e_shouhin_group
= a.e_shouhin_group )
上のSQLの処理を理解して下さい。説明を全て文章で羅列すると・・・・とても読めたものではないと思うので考えて下さい。
考えるネタは全て出しているはずですから。
ちなみにこれが理解できないから、データベースを操作できない、と言う訳ではありません。
貼り付けてから「編集」「SQL崩し」で整形して下さい。
SELECT |
実行結果は以下になります。
もし余裕があれば「e_shouhin_group」で「sh」は「シャツ」、「ts」は「Tシャツ」です。
各々で平均値を出してみて、実行結果がそれより価格が上かを確認してみて下さい。
副問い合わせで1行で処理するのがベターですが、数回に分けてSQLを発行すれば処理できるとも言えます。
個人的にですが、データベースを直で操作して集計する時に何回か分けても、それほどロスを出さないと思います。ケース・バイ・ケースですが。
難しい、1行のSQLを長い時間考え、調べるか・・・簡単なSQLを数回発行するか、
戻る