データベースの作表は色々な条件を様々な方法で抽出し複雑な内容を表現するにはいくつかの手法が用意されています。
プログラム言語として判断、ループ、集合等々を SQL文で表現することができます。
今回は、郵便番号データベースを利用しその挙動を確認します。
条件分岐、サブクエリの概要
条件分岐
値に対して条件分岐を行い異なる値を返すことができます。
単純にその値に応じて値を返します。
例えば、ある値が真偽（true,false)により名前をつけるます。
また、条件式により値を返します。
例えば、ある文字を含む場合、点数の範囲によるランキング等々
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// 単純な値と一致する場合の処理の実行 CASE 評価対象カラム名 WHEN 条件値1 THEN 処理 [WHEN 条件値2 THEN 処理] ... [ELSE 処理] END // 条件式による処理の実行 CASE WHEN 条件1 THEN 処理 [WHEN 条件2 THEN 処理] ... [ELSE 処理] END |
サブクエリ
異なる問い合わせを組み合わせ、複雑な問い合わせができるます。
select句、from句、having句、where句などで利用します。
ただ、対象のデータ件数が増えるにつれ速度低下、見通しが悪い等々の問題があります。
出来るだけテーブルの結合（JOIN句)利用したいです。
今回の課題
今回利用する郵便番号データベースの構造です。
全国の八地方として地域 (北海道、東北、関東、中部、近畿、中国、四国、九州沖縄)を定義してあります。
今回の課題として全国の政令都市として市町村としての区の数を調べることを課題とします。
順に追ってSQL文を作成していきます。
① 大阪府で市町村名に政令都市としての区で割り振られている地域の一覧
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
// 単純に市町村テーブルの市町村名に「区」が含まれている //一覧を都道府県を限定して表示してみます。 mysql> select * from city where pref_no=27 and city_name like '%区'; +-----------+---------+-----------------------+-----------------------------------------+ | city_code | pref_no | city_name | city_kana | +-----------+---------+-----------------------+-----------------------------------------+ | 27102 | 27 | 大阪市都島区 | オオサカシミヤコジマク | | 27103 | 27 | 大阪市福島区 | オオサカシフクシマク | | 27104 | 27 | 大阪市此花区 | オオサカシコノハナク | | 27106 | 27 | 大阪市西区 | オオサカシニシク | ・ ・ ・ | 27142 | 27 | 堺市中区 | サカイシナカク | | 27143 | 27 | 堺市東区 | サカイシヒガシク | | 27144 | 27 | 堺市西区 | サカイシニシク | | 27145 | 27 | 堺市南区 | サカイシミナミク | | 27146 | 27 | 堺市北区 | サカイシキタク | | 27147 | 27 | 堺市美原区 | サカイシミハラク | +-----------+---------+-----------------------+-----------------------------------------+ 31 rows in set (0.00 sec) |
② 東京都の市町村名で「区、市、町、村」で終わる件数
|
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 |
// ③ の結果を踏まえ都道府県の市町村で、「区、市、町、村」の件数を調べます。 // case構文を用いて、「区、市、町、村」を含む条件式に応じて、「区、市、町、村」の名前を定義します。 // 定義した「区、市、町、村」を元に分類し count()関数でカウントします。 mysql> select -> prefecture.pref_name, -> case -> when city.city_name like '%区' then '区' -> when city.city_name like '%市' then '市' -> when city.city_name like '%町' then '町' -> when city.city_name like '%村' then '村' -> else '他' -> end as bunrui, // 「区、市、町、村」を 変数名 「bunrui」とします。 -> count(*) // bunnrui の内容を元に分類し（group by)し、カウントします。 -> from city -> inner join prefecture on prefecture.pref_no=city.pref_no // 地域テーブルと市町村テーブルの内部結合 -> where city.pref_no=13 -> group by bunrui; +-----------+--------+----------+ | pref_name | bunrui | count(*) | +-----------+--------+----------+ | 東京都 | 区 | 23 | | 東京都 | 市 | 26 | | 東京都 | 町 | 5 | | 東京都 | 村 | 8 | +-----------+--------+----------+ 4 rows in set (0.00 sec) |
③ 全国の政令指定都市で設置された区が、地域名、都道府県毎の件数
都道府県と地域を結びづける多対多の構造を持ったテーブルを前の投稿で追加で作成しています。
このテーブルを利用してサブクエリとして、地域、都道府県別の「区」を含むリストを生成します。
|
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 |
// 地域名、都道府県毎の件数 mysql> select -> areapref.area_no, -> area.area_name, -> seireitoshi.pref_name, -> seireitoshi.cnt -> from -> ( select prefecture.pref_no,prefecture.pref_name ,count(prefecture.pref_no) as cnt -> from city -> inner join prefecture on prefecture.pref_no = city.pref_no -> where city.city_name like '%区' -> group by city.pref_no -> order by cnt desc ) as seireitoshi -> inner join areapref on seireitoshi.pref_no = areapref.pref_no -> inner join area on area.area_no = areapref.area_no -> order by seireitoshi.cnt desc; +---------+-----------------+--------------+-----+ | area_no | area_name | pref_name | cnt | +---------+-----------------+--------------+-----+ | 5 | 近畿 | 大阪府 | 31 | | 3 | 関東 | 神奈川県 | 25 | | 3 | 関東 | 東京都 | 23 | | 4 | 中部 | 愛知県 | 16 | | 8 | 九州・沖縄 | 福岡県 | 14 | | 5 | 近畿 | 京都府 | 11 | | 1 | 北海道 | 北海道 | 10 | | 3 | 関東 | 埼玉県 | 10 | | 4 | 中部 | 静岡県 | 10 | | 5 | 近畿 | 兵庫県 | 9 | | 6 | 中国 | 広島県 | 8 | | 4 | 中部 | 新潟県 | 8 | | 3 | 関東 | 千葉県 | 6 | | 2 | 東北 | 宮城県 | 5 | | 6 | 中国 | 岡山県 | 4 | +---------+-----------------+--------------+-----+ 15 rows in set (0.00 sec) |
④地域毎の市町村での「区」で終わる件数
|
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 |
// ④ を元に地域毎に集計してみました。 mysql> select -> areapref.area_no, -> area.area_name, -> sum(seireitoshi.cnt) -> from -> ( select prefecture.pref_no,prefecture.pref_name ,count(prefecture.pref_no) as cnt -> from city -> inner join prefecture on prefecture.pref_no = city.pref_no -> where city.city_name like '%区' -> group by city.pref_no -> order by cnt desc ) as seireitoshi -> inner join areapref on seireitoshi.pref_no = areapref.pref_no -> inner join area on area.area_no = areapref.area_no -> group by areapref.area_no; +---------+-----------------+----------------------+ | area_no | area_name | sum(seireitoshi.cnt) | +---------+-----------------+----------------------+ | 1 | 北海道 | 10 | | 2 | 東北 | 5 | | 3 | 関東 | 64 | | 4 | 中部 | 34 | | 5 | 近畿 | 51 | | 6 | 中国 | 12 | | 8 | 九州・沖縄 | 14 | +---------+-----------------+----------------------+ 7 rows in set (0.00 sec) |