Q&A
テーブル定義はCREATE TABLEに、テーブル中のデータはINSERTで https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正できるとSQLの動作確認が容易になるので、適切なコメントが付き易くなります。
下記のような問題のときに2018年に絞って各地域ごとのアイスの総売上をだしたいとします。
出力結果は、state_name, product_name, total_salesのカラムでだします。
このとき、私は下記記載のクエリでいけると思いましたが、これだと、2017年には購入していたplace.placeの場所の2018年の売上が0の場合は出力されません。
つまり、下記のテーブルでいうと、Chicagoのmellow candyが2018年は0なのにもかかわらず出力結果として下記のクエリだと出力されません。
そして、実際には無いデータを出力するようにクエリを書く必要があるのかなと思っています。
どなたか、こういった場合どうかけばいいのかご存知でしたらご教示頂けると幸いです。
mysql
1select 2pl.place as state_name, 3pr.name as product_name, 4sum(s.quantity*pr.price) 5from sales s 6left join price pr on pr.id = s.id 7left join place pl on pl.id = s.id 8where s.date = 2018 9group by state_name, product_name
priceテーブル
| id | name | price |
|---|---|---|
| 1 | ice candy | 60000 |
| 2 | coke candy | 50000 |
| 3 | candy | 20000 |
| 4 | mellow candy | 10000 |
| .... | ||
| .... | ||
| .... |
placeテーブル
| id | place |
|---|---|
| 1 | kansas |
| 2 | NewYork |
| 3 | Chicago |
| .... | |
| .... | |
| .... |
salesテーブル
| product_id | state_id | quantity | date |
|---|---|---|---|
| 2 | 2 | 2 | 2018 |
| 3 | 3 | 5 | 2019 |
| 2 | 3 | 2 | 2018 |
| 4 | 3 | 10 | 2017 |
| .... | |||
| .... | |||
| .... |
テーブルデータ結合の基本ができていない気がします。何を使って結合しているか、SQLとは別個で考えた方がいいのでは。
すみません、ご確認させてください。結合の基本について、どの部分でなぜできてないと思いましたでしょうか？そんなに間違えたクエリを書いていない気がしたのでとても気になっています。
パット見どれがマスタでどれがトランザクションなのか分かりづらいですし、結合している条件もなんか不自然です。SQLで考える前にテーブル正規化に基づいた設計から考え直してみた方がいいのではないかと思うわけです。
設計はもともとのありもののサンプルでもし、こういったテーブル定義があった場合にどうやって抽出対象を抽出するクエリを書くか、について悩んでいます。で、その場合にleft joinの部分が間違っているのかなと思ったのですが、具体的にわからずで、すみません、なぜ不自然になってると思いますでしょうか？
回答2件
0
Chicagoのmellow candyが2018年は0なのにもかかわらず出力結果として下記のクエリだと出力されません。
地域や商品を基準にしたいのに、SQLでの基準が売上になっているからです。
投稿2020/12/12 01:33
総合スコア25188
ありがとうございます。理解に近づけそうな気がしました。
つまり、下記のようなクエリを書いて、priceに対してsalesテーブルをleft joinすれば、salesテーブルにないprice idのものは、0としてレコードが追加されて出力される、という理解であっていますでしょうか？
select
pl.place as state_name,
pr.name as product_name,
sum(s.quantity*pr.price)
from price pr
left join sales s on pr.id = s.id
left join place pl on pl.id = s.id
where s.date = 2018
group by state_name, product_name
そういう事です。
price とplace の両方を基準にしたいなら、これらをcross joinしたものにsales をouter joinですね。
0
- カレンダーテーブルを別途用意するか
- プロシージャなので都度テンポラリーテーブルを作成するか
で対応できます
投稿2020/12/11 06:39
総合スコア114829
あなたの回答
tips
プレビュー
