FujiiRock回答ポイント 200ptウォッチ

sqlの質問です。

問い合わせ者TBLの中に、ユーザーの所在都道府県のカラムがあります。
それで、SQLで都道府県別問い合わせ件数を抽出した際に、
ユーザーの都道府県が、仮に東京だった場合は、
以下のような感じで都道府県→地域の順でかつ件数の昇順で表示される

エリア　　　件数
東京、　　　200
神奈川、　　130
千葉、 120
埼玉、 110
茨城、 50
群馬、 40
栃木、 30
東北地方 90
関西地方 180
中部地方 120
中国地方 100
北海道地方 80
九州地方 50

仮にユーザーの所在都道府県が大阪の場合は、
以下のような感じで都道府県→地域の順でかつ件数の昇順で表示される
大阪 200
京都 100
和歌山 90
岐阜 80
関東地方 130
中部地方 120
中国地方 100
北海道地方 80
九州地方 50

これを可能とするSQLがありましたら、教えていただきたいです。
どうぞよろしくお願い致します。

• はてなアカウントでログイン
• 無料ユーザー登録

ベストアンサー

• うぃんど2013/09/08 13:43:35

  満足200pt

  テスト環境が整わず php + SQLite3 でテストしました…。
  
  ＊＊＊の部分はユーザーの都道府県コードを入れます。

  SELECT
      area_name, sum(c_otoiawase) AS c_otoiawase
  FROM
  (
      SELECT 
           CASE WHEN c.chiki_cd = d.chiki_cd THEN -1 ELSE c.chiki_cd END AS area_cd,
           CASE WHEN c.chiki_cd = d.chiki_cd THEN a.todoufuken_name ELSE c.chiki_name END AS area_name,
           b.c_otoiawase
      FROM
          t_todoufuken AS a
      LEFT JOIN
          (
              SELECT
                  todoufuken_cd, count(*) AS c_otoiawase
              FROM
                  t_otoiawase
              GROUP BY
                  todoufuken_cd
          ) AS b ON b.todoufuken_cd = a.todoufuken_cd
      LEFT JOIN
          t_chiki AS c ON c.chiki_cd = a.chiki_cd
      LEFT JOIN
          t_todoufuken AS d ON d.todoufuken_cd = ＊＊＊
  ) AS x
  GROUP BY
      area_cd, area_name
  ORDER BY
      area_cd, sum(c_otoiawase) DESC
  ;
  

  スター

  • 

    5件のコメントを見る

  • うぃんど 2013/09/08 13:47:24

    ちなみにテストコードです。
    CREATE TABLEでintegerにしていないとソート順が変わるので注意してください。

    <?php
    # 環境設定
    ini_set('display_errors', 0);
    
    # ユーザーの都道府県コード＝１０だと仮定
    $todoufuken_cd = 10;
    
    # データベース準備（オンメモリ）
    $conn = new PDO('sqlite::memory:');
    
    # データ準備
    $conn->exec('CREATE TABLE t_otoiawase (todoufuken_cd)');
    for( $i = 1; $i < 500; $i++ ) {
        $conn->exec("INSERT INTO t_otoiawase VALUES(" . rand(1,47) . ")");
    }
    $conn->exec('CREATE TABLE t_chiki (chiki_cd integer, chiki_name text)');
    $conn->exec("INSERT INTO t_chiki VALUES ('0','北海道地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('1','東北地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('2','関東地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('3','中部地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('4','関西地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('5','中国地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('6','四国地方')");
    $conn->exec("INSERT INTO t_chiki VALUES ('7','九州地方')");
    $conn->exec('CREATE TABLE t_todoufuken (todoufuken_cd integer, todoufuken_name text, chiki_cd integer)');
    $conn->exec("INSERT INTO t_todoufuken VALUES ('1','北海道','0')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('2','青森県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('3','岩手県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('4','宮城県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('5','秋田県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('6','山形県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('7','福島県','1')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('8','茨城県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('9','栃木県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('10','群馬県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('11','埼玉県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('12','千葉県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('13','東京都','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('14','神奈川県','2')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('15','新潟県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('16','富山県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('17','石川県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('18','福井県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('19','山梨県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('20','長野県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('21','岐阜県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('22','静岡県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('23','愛知県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('24','三重県','3')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('25','滋賀県','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('26','京都府','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('27','大阪府','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('28','兵庫県','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('29','奈良県','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('30','和歌山県','4')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('31','鳥取県','5')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('32','島根県','5')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('33','岡山県','5')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('34','広島県','5')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('35','山口県','5')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('36','徳島県','6')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('37','香川県','6')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('38','愛媛県','6')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('39','高知県','6')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('40','福岡県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('41','佐賀県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('42','長崎県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('43','熊本県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('44','大分県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('45','宮崎県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('46','鹿児島県','7')");
    $conn->exec("INSERT INTO t_todoufuken VALUES ('47','沖縄県','7')");
    
    # 出力準備
    echo '<pre>';
    # 集計と出力
    foreach ($conn->query("
    SELECT
        area_name, sum(c_otoiawase) AS c_otoiawase
    FROM
    (
        SELECT 
             CASE WHEN c.chiki_cd = d.chiki_cd THEN -1 ELSE c.chiki_cd END AS area_cd,
             CASE WHEN c.chiki_cd = d.chiki_cd THEN a.todoufuken_name ELSE c.chiki_name END AS area_name,
             b.c_otoiawase
        FROM
            t_todoufuken AS a
        LEFT JOIN
            (
                SELECT
                    todoufuken_cd, count(*) AS c_otoiawase
                FROM
                    t_otoiawase
                GROUP BY
                    todoufuken_cd
            ) AS b ON b.todoufuken_cd = a.todoufuken_cd
        LEFT JOIN
            t_chiki AS c ON c.chiki_cd = a.chiki_cd
        LEFT JOIN
            t_todoufuken AS d ON d.todoufuken_cd = $todoufuken_cd
    ) AS x
    GROUP BY
        area_cd, area_name
    ORDER BY
        area_cd, sum(c_otoiawase) DESC
    ;
    ") as $row) {
        print_r($row);
    }
    print_r($conn->errorInfo());
    echo '</pre>done.';
    
    unset($conn);
    ;
    

    スター

  • うぃんど 2013/09/08 14:11:30

    ×ソート順が変わる
    ○正常に機能しない

    スター

  • うぃんど 2013/09/08 16:42:08

    回答本文で＊＊＊の部分を書き換えました。

    スター

  • FujiiRock 2013/09/08 19:15:48

    ありがとうございます！！！
    検証させていただきます(^^ゞ

    スター

  • FujiiRock 2013/09/14 08:27:35

    検証が遅くなってしまい失礼しました..> 素晴らしいです～！！ありがとうございました！

    スター

その他の回答

• FujiiRock

  2013/09/08 04:15:07

  所在都道府県のカラムには都道府県コードが入っています。

  Excel+SQLiteです。

  質問内容について訂正します。

  誤り）都道府県→地域の順でかつ件数の昇順

  正しい）で都道府県→地域の順でかつ件数の降順

  >実際のテーブル名ならびにカラム名と、

  >都道府県と地方を結びつけるための情報はどこにあるのかも教えてください。

  ＜問い合わせTBL＞

  テーブル名：t_otoiawase

  カラム名：todoufuken_cd

  ＜都道府県TBL＞

  todoufuken_cd

  todoufuken_name

  chiki_cd

  ＜地域TBL>

  chiki_cd

  chiki_name

  
  
  

  >SQLを実行する前の段階でわかっているのはユーザー（コード？）だけですか？

  >ユーザーの都道府県コードもすでにわかっているのですか？

  SQLを実行する前には既に問い合わせTBLに登録されている

  問い合わせ者の都道府県がわかっています。

  >「都道府県別問い合わせ件数」は問い合わせ者TBLから集計するので良いですよね？

  はい。OKです！

• きゃづみぃ2013/09/08 08:10:48

  0pt

  ソート用のカラムを作って それでソートさせたほうが カンタンです。
  レコード数もそんなにないと思いますので、こまかいソート順を設定できていいかと思いますよ。

  スター

この質問へのコメント

• うぃんど2013/09/08 04:33:38
  条件が色々不足してるので…。
  
  php+SQLite3で良いですか？Accessですか？MySQLですか？PostgreSQLですか？
  （Accessだと再起動して環境変えなきゃならないのでかなりあとになります…。）
  
  文章は昇順で、実例は降順ですが、どちらが正解ですか？
  
  実際のテーブル名ならびにカラム名と、
  都道府県と地方を結びつけるための情報はどこにあるのかも教えてください。
  （別途テーブルを作る必要があれば、用意してください）
  
  SQLを実行する前の段階でわかっているのはユーザー（コード？）だけですか？
  ユーザーの都道府県コードもすでにわかっているのですか？
  
  「都道府県別問い合わせ件数」は問い合わせ者TBLから集計するので良いですよね？
  
  まだ何か聞き漏らしが有るような無いような…。
• うぃんど2013/09/08 05:12:28
  以前のVBA+ODBC+SQLiteですね…。
  
  効率良いのが浮かばないので、少々時間くださいね…。
• きゃづみぃ2013/09/14 09:16:59
  大阪 200
  京都 100
  和歌山 90
  岐阜 80
  
  というのは どういう順番なんでしょう?
  

質問の情報

登録日時

2013-09-08 03:33:33

終了日時

2013-09-14 08:27:57

回答条件

1人5回まで

この質問のカテゴリ

• コンピュータ
• ウェブ制作

この質問に含まれるキーワード

人気の質問

• この英文どうなってますか？ The boy seemed to be waiting for his mother to come.（その少年は母親が来…匿名質問者112日前
• 不定詞の形容詞的用法らしいんですけど、副詞的用法と書いたら✖️ですか？ I want to buy a book to read …匿名質問者112日前
• 日向陽葵という○V女優が好きなのですが似ている○V女優はいますか匿名質問者17時間前
• 共通テストで使われるプログラミング言語は共通テスト特有の言語と聞いたのですが、受験勉強で身につけたプ…匿名質問者117時間前
• 内定してほしいポケモンはいますか？匿名質問者17時間前

メニュー

• 質問一覧
• カテゴリ一覧
• 無料ユーザー登録
• 人力検索はてなトップ

PC版

• お問い合わせ
• ヘルプ
• お知らせ
• はてなトップ