人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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がありましたら、教えていただきたいです。
どうぞよろしくお願い致します。

●質問者: FujiiRock
●カテゴリ:コンピュータ ウェブ制作
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

質問者から

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

質問内容について訂正します。
誤り)都道府県→地域の順でかつ件数の昇順
正しい)で都道府県→地域の順でかつ件数の降順

>実際のテーブル名ならびにカラム名と、
>都道府県と地方を結びつけるための情報はどこにあるのかも教えてください。
<問い合わせTBL>
テーブル名:t_otoiawase
カラム名:todoufuken_cd

<都道府県TBL>
todoufuken_cd
todoufuken_name
chiki_cd

<地域TBL>
chiki_cd
chiki_name



>SQLを実行する前の段階でわかっているのはユーザー(コード?)だけですか?
>ユーザーの都道府県コードもすでにわかっているのですか?
SQLを実行する前には既に問い合わせTBLに登録されている
問い合わせ者の都道府県がわかっています。

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

はい。OKです!


1 ● きゃづみぃ
●0ポイント

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


2 ● うぃんど
●200ポイント ベストアンサー

テスト環境が整わず 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
;

うぃんどさんのコメント
ちなみにテストコードです。 CREATE TABLEでintegerにしていないとソート順が変わるので注意してください。 >|php| <?php # 環境設定 ini_set('display_errors', 0); # ユーザーの都道府県コード=10だと仮定 $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); ; ||<

うぃんどさんのコメント
×ソート順が変わる ○正常に機能しない

うぃんどさんのコメント
回答本文で***の部分を書き換えました。

FujiiRockさんのコメント
ありがとうございます!!! 検証させていただきます(^^ゞ

FujiiRockさんのコメント
検証が遅くなってしまい失礼しました..>< 素晴らしいです〜!!ありがとうございました!
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ