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

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

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

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

JOIN

これはSQL文のJOINに関するタグです。リレーショナルデータベースシステムの二つ以上のテーブルを結合する際に、この構文が利用されます。

mysqli

MySQLiはPHP5より導入されているデータベース用のドライバです。MySQL 4.1.3以降の新しい機能の利点をまとめています。

Q&A

解決済

2回答

7133閲覧

SQL join 片方のテーブルに存在しないデータをカウント0で集計したい

kitsu

総合スコア2

MySQL

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

JOIN

これはSQL文のJOINに関するタグです。リレーショナルデータベースシステムの二つ以上のテーブルを結合する際に、この構文が利用されます。

mysqli

MySQLiはPHP5より導入されているデータベース用のドライバです。MySQL 4.1.3以降の新しい機能の利点をまとめています。

0グッド

0クリップ

投稿2020/07/03 06:45

前提・実現したいこと

[商品(マスタ)]と[購入(データ)]のテーブルから、返品されなかった商品の数量を
count()関数で求めるSQL文を作成しています。
[購入(データ)]に商品のレコードがない場合にでも、[期待する結果]のように数量を0として
結果を求めたいのですが、条件に返品なしの商品(返品=0)を追加すると、
数量0の結果が表示されなくなります。
JOIN部分を色々と変えて試してみたのですが期待する結果が得られません。
JOINを使うのは間違えなのでしょうか?ご教授いただければ幸いです。

[商品(マスタ)]

商品番号商品名
1リンゴ
2バナナ
3ミカン

[購入(データ)]

購入番号商品番号返品
110
210
311
430
531

[期待する結果]
商品(マスタ)にある商品で、返品されなかった商品(購入.返品=0)の集計を求める

数量商品番号商品名
21リンゴ
02バナナ
13ミカン

発生している問題・エラーメッセージ

期待する結果を求めるSQLを実行したのですが、下記の結果のように、バナナの数量0のレコードが出力されません。

数量商品番号商品名
21リンゴ
12ミカン

該当のソースコード

SELECT COUNT(k.購入番号) AS 数量, s.商品番号, s.商品名, FROM 購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 WHERE k.返品 = 0 GROUP BY k.商品番号

試したこと

WHERE条件を除くとバナナの数量0のレコードが出力されます。

SELECT COUNT(k.購入番号) AS 数量, s.商品番号, s.商品名, FROM 購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 GROUP BY k.商品番号
数量商品番号商品名
31リンゴ
02バナナ
23ミカン

補足情報(FW/ツールのバージョンなど)

サーバのバージョン: 10.4.11-MariaDB - mariadb.org binary distribution
データベースクライアントのバージョン: libmysql - mysqlnd 7.4.2

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

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

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

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

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

guest

回答2

0

ベストアンサー

SQL

1SELECT sum(case when k.返品=0 then 1 else 0 end) AS 数量, s.商品番号, s.商品名 2FROM 購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 3GROUP BY S.商品番号, s.商品名

こっちでも大丈夫かも ← 駄目ですね。。

SQL

1SELECT count(k.返品=0) AS 数量, s.商品番号, s.商品名 2FROM 購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 3GROUP BY S.商品番号, s.商品名

count()ならこうですね。

SQL

1SELECT count(k.返品) AS 数量, s.商品番号, s.商品名, 2FROM 購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 and k.返品=0 3GROUP BY k.商品番号

投稿2020/07/03 07:16

編集2020/07/03 08:37
sazi

総合スコア25138

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

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

kitsu

2020/07/03 07:37 編集

sazi様 お教えいただいたSQLで無事に望む結果が得られました。本当にありがとうございます。 恐れ入りますが、後学のために、ここで教えてほしいのですが、今回のような場合、 whereで条件を設定すると、数量0のような集計は無理なのでしょうか? というのはレコード件数が多くなった場合、sum()やcase分を1行ごとに行うのは、 スピード的に遅くなったりしないものなのでしょうか?
kitsu

2020/07/03 07:44

SELECT count(k.返品=0)~ の分でも正しい結果が得られました。ありがとうございます。
sazi

2020/07/03 07:45 編集

inner join なら返品=0でも良いですけど、outer joinの場合はNull考慮が必要になります。 coalesce(返品, 1)=0とかしないと駄目です。 若しくはインラインビューで返品=0のものとの結合にするとか。 > レコード件数が多くなった場合、sum()やcase分を1行ごとに行うのは、スピード的に遅くなったりしないものなのでしょうか? データの分布やインデックスへの依存の方が大きいかと思います、
sazi

2020/07/03 07:51 編集

SELECT count(k.返品=0)はk.返品=1の場合もカウントするから駄目ですね。 SELECT count(k.返品)と同義です
kitsu

2020/07/03 07:52

今回の案件では頂きましたご回答で十分な結果が得られております。今後修正することを考慮して、インラインビューを使ってする方法にチャレンジしてみます。
kitsu

2020/07/03 07:55

SELECT count(k.返品=0)で再確認したら、カウントがリンゴ:3,バナナ:0,ミカン:2になってました。バナナ:0が出てて安心しちゃいました。
sazi

2020/07/03 08:39 編集

結合条件だけでも大丈夫だと思います。(追記しました)
kitsu

2020/07/03 08:51

結合条件にしたら行けるんですね!勉強になります。私はwhere条件にk.返品=0を設定していたので、ダメだったんですね。 さっきヒントを頂いた、インラインビューで試したら、こちらでもできました。その時のSQLです。 select s.商品名, count(k.購入番号) from (SELECT * FROM `購入` WHERE `返品` = 0) as k RIGHT JOIN 商品 as s ON k.商品番号 = s.商品番号 group by s.商品番号 色々と勉強させた頂いて感謝です。
guest

0

とりあえず、

SQL

1RIGHT OUTER JOIN 23LEFT OUTER JOIN 4 5それと、 6 7, があるとエラーになります。 8SELECT ... s.商品名, FROM 購入 ... 9

最初から質問のテーブルの定義はCREATE TABLE文に変更し、テーブル中のデータはINSERT文で提示できたがSQLで動作確認できるので適切なコメントが付き易いです。

投稿2020/07/03 07:12

Orlofsky

総合スコア16415

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

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

sazi

2020/07/03 07:18

商品マスタを基準にするから、Right Joinは正しいと思いますよ。
kitsu

2020/07/03 07:27

Orlofsky様 早速のご回答ありがとうございました。 ご教授頂きました、LEFT OUTER JOINと、 「SELECT ... s.商品名, FROM 購入 」の「s.商品名, 」カンマと削除して 試してみましたが、やはり数量0の集計が出てきませんでした。 インデックスとか関係あるのでしょうか? ちなみに、商品(マスタ)は商品番号に購入(データ)は購入番号に、 PRIMARYインデックスを付けております。
Orlofsky

2020/07/03 07:40

saziさん、ご指摘ありがとうございます。勘違いしていました。
Orlofsky

2020/07/03 07:42

kitsuさん 質問は修正できます。 CREATE TABLE文とINSERT文の提示と余計な , を削っては?
kitsu

2020/07/03 07:48

Orlofsky様 失礼しました。次回から質問文の間違いは訂正いたしますね。 CREATE TABLE文とINSERT文の提示も、回答者様にお手間を取らせないように するためには大切ですね。次回から提示させていただきます。 勉強になりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問