質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.77%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 410

chapp

score 219

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

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

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

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

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

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

■地域テーブル(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 '所在地1',
  `master_addr2` varchar(255) NOT NULL COMMENT '所在地2',
  `master_addr3` varchar(255) NOT NULL COMMENT '所在地3'
) 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');

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2019/05/09 09:14 編集

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

    キャンセル

  • sazi

    2019/05/09 09:19

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

    キャンセル

回答 3

checkベストアンサー

0

物件マスターテーブルの設計は見直した方が良いですね。
地域テーブルの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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/09 11:31

    saziさん

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

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


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

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



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

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



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

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

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

    なので、建物が3つある場合は re_master_rentのレコード数は3つで、その建物内にある部屋の数の合計が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


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

    キャンセル

  • 2019/05/09 12:32

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

    キャンセル

  • 2019/05/09 13:50

    saziさん

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

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

    キャンセル

0

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/09 11:35

    yambejpさん

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/09 11:38

    orlofskyさん

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

    キャンセル

  • 2019/05/09 11:42

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

    キャンセル

  • 2019/05/09 13:51

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.77%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る