サブクエリによる集計を行いたい

Question

お世話になります。SuumoやHOME`Sなどでよく見かける不動産ポータルで

千代田区(894) 中央区(332) 港区(979)

新宿区(446) 　文京区(112) 　台東区(801)

墨田区(468) 　江東区(463) 品川区(476)

といったような表示をしたいと考えています。

テーブルは３つあり、構成は以下のような内容です。

■地域テーブル（zip）
zip_no　addr1　　addr2　　addr_no

1　　北海道　札幌市中央区　　1

2　　北海道　札幌市北区　　　1

3　　北海道　札幌市東区　　　1

4　　北海道　札幌市白石区　　1

5　　北海道　札幌市豊平区　　1

6　　東京都　千代田区　　　　13

7　　東京都　中央区　　　　　13

8　　東京都　港区　　　　　　13

9　　東京都　新宿区　　　　　13

10　 東京都　文京区　　　　　13

11　東京都　台東区　　　　　13

12　東京都　墨田区　　　　　13

13　東京都　江東区　　　　　13

13 東京都　品川区　　　　　13

■物件マスターテーブル（re_master_rent）
master_no master_property_name master_addr1 master_addr2 master_addr3

1　　豊島レジデンス　　　13　　　豊島区　　本町1-7

2　　品川マンション　　　13　　　品川区　　本町4-4

3　　中央レジデンス　　　13　　　中央区　　本町21-15

4　　新宿アパート　　　　13　　　新宿区　　本町21-12

5　　渋谷マンション　　　13　　　渋谷区　　本町7

※物件データを格納するテーブルで、集合住宅などで所在地など共通するデータを格納
master_addr1カラムは、zipテーブルのaddr_noカラムと紐づく

■物件詳細テーブル（re_property_data）
data_no　　data_master_no　　data_property_name　　data_price

1　　　　1　　　310号室　　　248000

2　　　　1　　　311号室　　　148000

3　　　　3　　　210号室　　　248000

4　　　　3　　　211号室　　　148000

5　　　　3　　　213号室　　　248000

6　　　　2　　　201号室　　　148000

※集合住宅に紐づく部屋のデータを格納。
物件マスターテーブル（re_master_rent）の master_no と、物件詳細テーブル（re_property_data）の data_master_noが紐づいている。

このような構成となっています。

実現したいことは、zipテーブルの都道府県である13を呼出したとき、同テーブルのaddr2を一覧で表示させ、物件マスターテーブル（re_master_rent）の master_addr1カラムが13であるもの、かつその物件に紐づく各部屋の数を求めたいわけですが、物件マスターテーブル（re_master_rent）と物件詳細テーブル（re_property_data）をJOINさせ、どのようなSQLとなるのか？が分からず質問させて頂きました。

地域テーブル（zip）と物件マスターテーブル（re_master_rent）だけなら以下のようなSQLで出来るまで確認しているのですが、解決に向けご教示いただければ幸いです。

SELECT 
zip_no, 
addr2, 
addr_no, 
IFNULL(tmp.cnt, 0) AS cnt 
FROM zip 
LEFT JOIN (SELECT data_youtube, data_addr2, data_update_plan_date, COUNT(*) AS cnt FROM 
re_master_rent GROUP BY master_addr2) AS tmp ON zip.addr2 = tmp.master_addr2
WHERE addr_no = '13' ORDER BY zip_no

ちなみに・・・各テーブルは以下のような構成です。（今回の質問に不必要なカラムは省いています）

CREATE TABLE `zip` (
  `zip_no` int(11) NOT NULL,
  `addr1` varchar(255) DEFAULT NULL,
  `addr2` varchar(255) DEFAULT NULL,
  `addr_no` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';



INSERT INTO `zip` (`zip_no`, `addr1`, `addr2`, `addr_no`) VALUES
(1, '北海道', '札幌市中央区', 1),
(2, '北海道', '札幌市北区', 1),
(3, '北海道', '札幌市東区', 1),
(4, '北海道', '札幌市白石区', 1),
(5, '北海道', '札幌市豊平区', 1),
(6, '東京都', '千代田区', 13),
(7, '東京都', '中央区', 13),
(8, '東京都', '港区', 13),
(9, '東京都', '新宿区', 13),
(10, '東京都', '文京区', 13),
(11, '東京都', '台東区', 13),
(12, '東京都', '墨田区', 13),
(13, '東京都', '江東区', 13),
(13, '東京都', '品川区', 13);


CREATE TABLE `re_master_rent` (
  `master_no` int(11) NOT NULL COMMENT '連番',
  `master_cate_m_no` int(11) NOT NULL COMMENT '物件カテゴリ',
  `master_addr1` varchar(255) NOT NULL COMMENT '所在地１',
  `master_addr2` varchar(255) NOT NULL COMMENT '所在地２',
  `master_addr3` varchar(255) NOT NULL COMMENT '所在地３'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `re_master_rent` (`master_no`, `master_property_name`, `master_addr1`, `master_addr2`, `master_addr3`) VALUES
(1, '豊島レジデンス', '13', '豊島区', '本町1-7'),
(2, '品川マンション', '13', '品川区', '本町4-4'),
(3, '中央レジデンス', '13', '中央区', '本町21-15'),
(4, '新宿アパート', '13', '新宿区', '本町21-12'),
(5, '渋谷マンション', '13', '渋谷区', '本町7');



CREATE TABLE `re_property_data` (
  `data_no` int(11) NOT NULL,
  `data_master_no` int(11) NOT NULL,
  `data_property_name` varchar(255) NOT NULL,
  `data_price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `re_property_data` (`data_no`, `data_master_no`, `data_property_name`, `data_price`) VALUES
(1, 1, '310号室', '248000'),
(2, 1, '311号室', '148000'),
(3, 3, '210号室', '248000'),
(4, 3, '211号室', '148000'),
(5, 3, '213号室', '248000'),
(6, 2, '201号室', '148000');

お忙しい中恐縮ですが、ご指導のほどよろしくお願いいたします。

Comments

[sazi]

（今回の質問に不必要なカラムは省いています）
エラーにならないように省いてください。

[sazi]

物件0も抽出したいのでしょうか？

Answer 1

不動産関係は合併も頻繁にありますから、まずテーブルの設計をきちんとやりましょう。
全国地方公共団体コード 都道府県、市区町村
郵便番号データダウンロード
質問のテーブル設計を修正されては？

Comments

[chapp]

orlofskyさん

貴重なご意見ありがとうございます。
お手数かと思いますが、具体例などお教えいただけるとありがたいです。
私なりに考えての構成だったので、理解を深めたいと考えてのお願いです。

[Orlofsky]

提示した情報でテーブル定義ができなかったら能力が足りません。有償で人を雇ってください。

[chapp]

ありがとうございました。

Answer 2

物件マスターテーブルの設計は見直した方が良いですね。
地域テーブルのzip_noとリレーションされるべきです。　

この状態だと地域テーブルのaddr1から、都道府県名を除外したものと、物件マスターのmaster_addr2をつなぐ必要があり、低速な状態です。

以下はzip.addr1を全角空白で区切り後半部分を結合条件としたものです。　　

SELECT zip_no, SUBSTRING_INDEX(zip.addr2, '　',-1), count(rent.*) AS cnt 
FROM  zip LEFT JOIN re_master_rent rent
      ON    zip.addr_no=rent.master_addr1
        and SUBSTRING_INDEX(zip.addr2, '　',-1) = rent.master_addr2
WHERE zip.addr_no = '13'
group by zip_no, zip.addr2
ORDER BY zip_no

訂正

都道県名とは分離されていましたね。勘違いしていました。

SELECT zip_no, zip.addr2, count(rent.*) AS cnt 
FROM  zip LEFT JOIN re_master_rent rent
      ON    zip.addr_no=rent.master_addr1
        and zip.addr2 = rent.master_addr2
WHERE zip.addr_no = '13'
group by zip_no, zip.addr2
ORDER BY zip_no

追記

SELECT zip_no, addr1, addr2, addr_no, temp.cnt 
FROM  zip inner join (
        select master_addr1, master_addr2, count(*) as cnt
        from  re_master_rent rent inner join re_property_data prp
              ON prp.data_master_no = rent.master_no
        group by master_addr1, master_addr2
      ) as temp
      ON    zip.addr_no=temp.master_addr1
        and zip.addr2 = temp.master_addr2
WHERE zip.addr_no = '13'
ORDER BY zip_no

Comments

[chapp]

saziさん

お忙しい中での回答、ありがとうございます。
色々と書き込んで下さり恐縮しています。

当方の質問に対するご質問もありましたので、まずはそちらを回答すると、

>エラーにならないように省いてください。

質問時の提示したcreate文、insert文ですが、こちらでもphpMyAdmin上で実行したら初めはエラーとなりましたがこのことでしょうか？
しかし、再度実行したら正常に登録されて・・何が原因だったのか？？です。

>物件0も抽出したいのでしょうか？

今のところ0件の場合は非表示でと考えています。

ところで、回答下さったSQLを試していました。
一部修正をしてみましたが、思うような結果を得られずにいます。

当初の質問の説明が上手くできずに申し訳ありません。再度説明させてください。

物件のデータ構成は、re_property_dataと、re_master_rentの2つから成り立っています。
re_master_rentは集合住宅の基本情報となるデータで、re_property_dataはその建物内にある各部屋の情報です。

なので、建物が3つある場合は re_master_rentのレコード数は３つで、その建物内にある部屋の数の合計が20部屋あったら、re_property_dataのレコード数も20という形になり、今回、求めたい表示は、部屋の数を地区名と共に表示させたいと考えています。

ちなみに、お恥ずかしながら当方が試行錯誤ながらに試しているSQLは以下の通りですが、上手く行っていません。

SELECT
zip_no,
addr1,
addr2,
addr_no,
IFNULL(tmp.cnt, 0) AS cnt
FROM zip
LEFT JOIN (
SELECT
re_property_data.data_no,
re_property_data.data_master_no,
re_master_rent.master_no,
re_master_rent.master_cate_m_no,
re_master_rent.master_addr1,
re_master_rent.master_addr2,
COUNT(*) AS cnt
FROM re_property_data
LEFT JOIN re_master_rent ON re_property_data.data_master_no = re_master_rent.master_no
) AS tmp ON zip.addr2 = tmp.master_addr2
WHERE addr_no = '13' ORDER BY zip_no

引き続き、ご指導のほど頂戴出来れば幸いです。
よろしくお願いいたします！

[sazi]

>今のところ0件の場合は非表示でと考えています。
との事なので結合はinner join にした内容で追記しています。

[chapp]

saziさん

お忙しい中でのご対応、ありがとうございました。
0件の表示も出来ました。

テーブルの設計に不十分なところあるとは認識していますが、この度の質問、解決出来ましたこと御礼いたします。ありがとうございました！

Answer 3

re_master_rentテーブルのmaster_cate_m_no(int)カラムに対して
master_property_nameという間違ったカラム名を指定して
文字列データをinsertしようとしていますが大丈夫でしょうか？
それとvaluesが抜けています

Comments

[chapp]

yambejpさん

お忙しい中での書込みをありがとうございます。
誤った記述がありますね。不必要なカラムを省いていてのミスかと思います。見直してみます。