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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

4077閲覧

外部結合に条件を付与すると外部結合の意味がなくなって困っています

PenelopeG

総合スコア31

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2017/07/25 02:12

MySQL 5.6.25で開発テストしています。

###実現したいこと
ストアテーブル(Stores)と市区町村マスタ(Cities)があります。
市区町村毎にストア件数を取得しようとしています。
条件は、
・ストアテーブルCityIDの先頭2桁が「28」
・ストアテーブルPulishedが「1」
並び順は、
・市区町村マスタKanaの昇順。

###テーブル情報
■ストアテーブル(Stores)データ例

StoreIDNameCityIDPulished
1あいうえお店2810001
2かきくけこ店2810001
3さしすせそ店2820650
4たちつてと店2820221
  • 2,000件程あります。
  • 先頭2桁が「28」以外のデータもあります。

■市区町村マスタ(Cities)データ例

CityIDNameKana
281000神戸市コウベシ
282065芦屋市アシヤシ
282049西宮市ニシノミヤシ
282022尼崎市アマガサキシ
  • 全国の市区町村マスタです。
  • 1,900件弱あります。
  • CityIDは必ず6桁です。
  • CityIDの先頭2桁が「28」以外のデータもあります。

###取得したい結果

CityIDCityNameStoreCount
282065芦屋市0
282022尼崎市1
281000神戸市2
282049西宮市0

###試したSQL

sql

1SELECT 2 s.CityID AS CityID 3 ,c.Name AS CityName 4 ,( 5 CASE 6 WHEN s.CityID IS NULL THEN 0 7 ELSE COUNT(s.CityID) 8 END 9 ) AS StoreCount 10FROM 11 Stores AS s 12 LEFT OUTER JOIN 13 Cities AS c 14 ON c.CityID = s.CityID 15WHERE 16 SUBSTR(s.CityID, 1, 2) = '28' 17AND 18 s.Published = '1' 19GROUP BY 20 s.CityID 21ORDER BY 22 c.Kana 23;

###発生している問題
上記SQLでは0件の市区町村が表示されません。
WHERE句の「s.Published = '1'」を条件から除くと0件の市区町村が表示されますが、これではカウントしたくないストアも数えてしまうので意味がありません。
ご教授いただければ助かります。よろしくお願いします。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

hihihijijiさんの回答で解決だと思うのですが、なぜLEFT JOINでうまくいかなかったのかの説明です。

左右が逆です。cities left join stores でないといけません。left joinは左側が保たれるのです。

投稿2017/07/25 02:38

yuba

総合スコア5568

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PenelopeG

2017/07/25 03:18

補足説明までいただけるとは思ってもいませんでした。 外部結合がまだきちんと理解できていないと実感しました。 もっと勉強したいと思います。 ご丁寧にありがとうございました!
guest

0

ベストアンサー

こうかな?

SQL

1SELECT 2 c.CityID AS CityID 3 ,c.Name AS CityName 4 ,( 5 SELECT COUNT(1) 6 FROM 7 Stores AS s 8 WHERE 9 s.CityID = c.CityID 10 AND 11 s.Pulished = 1 12 ) AS StoreCount 13FROM 14 Cities AS c 15WHERE 16 c.CityID LIKE '28%' 17ORDER BY c.Kana

投稿2017/07/25 02:29

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PenelopeG

2017/07/25 03:16

試してみたところ、思い通りの結果が得られました。 「c.CityID LIKE '28%'」などわかりやすい記述で、そのあたりも勉強になりました。 ありがとうございました!!!
guest

0

sumにて件数をカウントしてみました

sql

1select c.CityID, c.Name CityName, sum(IF(s.Published=1,1,0)) StoreCount 2from Cities c left join Stores s 3on c.CityID=s.CityID 4where substr(c.CityID,1,2)='28' 5group by 1,2 6order by 1 7;

投稿2017/07/25 03:40

A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PenelopeG

2017/07/25 04:35

私の考えていたSQLで、 LEFT JOINの左右が逆であったという点はyubaさんの回答で理解したのですが、 しっくりきていなかった CASE WHEN s.CityID IS NULL THEN 0 ELSE COUNT(s.CityID) END の部分と、 WHERE句の「s.Published = '1'」という条件の書き方の部分において、 SQL初心者なもので、件数を取得するのにCOUNTではなくSUMを使用して、 条件をIFで書くということは目から鱗状態で衝撃でした。 考え方としては、当初やりたかったことと似ているためわかりやすい!と思いました。 実際に試してみたところ、こちらもまた思い通りの結果が得られました。 (ORDER BYのところだけ、ORDER BY c.Kanaに変更して実行しました。) 初心者ゆえ、先にいただいた回答と、どちらのSQLがベストなのか判断ができないのですが とても勉強になりました。ありがとうございました。 ベストアンサーにつきましては、先につけさせていただいていたので、そのままにさせていただいておりますが、新たなこの回答にも感謝です。
A.Ichi

2017/07/25 05:02

count()は、null値を数えないのでCOUNT(s.CityID)だけでも行けると思います。ifでなく、case文でも同様な結果が得られます。他のDBの場合に使えます。
PenelopeG

2017/07/25 05:51 編集

なるほど。count()はnull値を数えないということがわかっていませんでした。 おっしゃるとおり、 sum(IF(s.Published=1,1,0)) StoreCount の部分を SUM( CASE WHEN s.Published = '1' THEN 1 ELSE 0 END ) StoreCount として試してみても上手くいきました。 追加情報ありがとうございました。
guest

0

例示のものが実際に結果を表示できるものではないので正しく回答できませんが
結果に対してright joinで市区町村テーブルをつないでください
より正確な回答を希望する場合は
create table & insert intoで想定するテーブルを例示し直して下さい

投稿2017/07/25 02:21

編集2017/07/25 02:23
yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PenelopeG

2017/07/25 03:13

ご回答いただきありがとうございます。 今回は、hihihijijiさんの回答で解決させていただきましたが、結果に対してright joinという考えには私は至っておらず、勉強になりました。 また、質問の方法において、今後の参考にもさせていただきたいと思います。 ご指摘ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問