売上管理

質問

お世話になっております。
勉強がてらネットショップを作っているのですが、売上一覧を表示すべくところで立ち止まってしまいました。

以下にテーブルの構成および得たい結果を書き残しますので、ご教示頂けた幸いです。（MySQL5)

テーブル名：member
member_no　　name1
　1　　　　　　佐藤
　2　　　　　　小島
　3　　　　　　石田
　4　　　　　　鈴木

テーブル名：sales
sales_no　sale_slip_no　sales_date　　member_no　goods_name　request_money　receipt_maney
　1　　　　　1　　　　　2013-6-6　　　　　2　　　　　キュウリ　　　　　　5250
　2　　　　　1　　　　　2013-6-6　　　　　2　　　　　イチゴ　　　　　　　3150
　3　　　　　1　　　　　2013-6-6　　　　　2　　　　　バナナ　　　　　　　8400
　4　　　　　2　　　　　2013-6-7　　　　　1　　　　　りんご　　　　　　　5250
　5　　　　　2　　　　　2013-6-7　　　　　1　　　　　　　　　　　　　　　　　　　　　　　　　3000
　6　　　　　3　　　　　2013-6-8　　　　　4　　　　　ほうれん草　　　　　9450
　7　　　　　3　　　　　2013-6-8　　　　　4　　　　　しいたけ　　　　　　2100
　8　　　　　4　　　　　2013-6-8　　　　　3　　　　　マンゴー　　　　　　6300

・各テーブルにあるmember_noカラムは対となる紐付くデータです。
・sale_slip_noは伝票番号です。
・request_moneyは商品単位での小計金額です。
・receipt_maneyは入金金額（またはポイントが支払いに使われた時の金額）です。
・質問と無関係と思われるカラムは省略しています。

以上のような構成のテーブルがありますが、以下のように伝票番号毎に別け、かつ伝票毎の合計額を求めたいと考えています。

sale_slip_no　sales_date　　name　　request_money
　4　　　　　　2013-6-8　　　石田　　　　6300
　3　　　　　　2013-6-8　　　鈴木　　　　11550
　2　　　　　　2013-6-7　　　佐藤　　　　2250
　1　　　　　　2013-6-6　　　小島　　　　16800

以上ですが、join group sumの組み合わせになるのかな・・と考え調べ、試行錯誤を繰り返していますが、描いている結果を得ることが出来ません。どのようなSQLになるのかご教示いただければ幸いです。

また、売上データとして蓄積させるテーブル構成を作ったのは初めてですが、もっと適切なものがあれば、こちらも併せてアドバイスを頂けると幸いです。

お忙しいなか恐縮ですが、宜しくお願い申し上げます。

回答No.4

yambejp · ベストアンサー

#3です

たとえばこんな感じで検討してみてはどうでしょうか？
テーブル、member=販売先管理テーブル,slip=伝票管理テーブル,sales=売上集計テーブル

create table member(member_no int,name varchar(20),index(member_no,name));
insert into member values(1,'佐藤'),(2,'小島'),(3,'石田'),(4,'鈴木');

create table slip (slip_no int,member_no int,sales_date date,index(slip_no,member_no,sales_date));
insert into slip values(1,2,'2013-06-06'),(2,1,'2013-06-07'),(3,4,'2013-06-08'),(4,3,'2013-06-08');

create table sales(sales_no int,sale_slip_no int,goods_name varchar(30) null,request_money int null,receipt_money int null,index(sale_slip_no,request_money,receipt_money));
insert into sales values(1,1,'キュウリ',5250,null),(2,1,'イチゴ',3150,null),(3,1,'バナナ',8400,null),(4,2,'りんご',5250,null),(5,2,null,null,3000),(6,3,'ほうれん草',9450,null),(7,3,'しいたけ',2100,null),(8,4,'マンゴー',6300,null);

//販売一覧を表示
select sales_no,sale_slip_no,sales_date,member_no,goods_name,request_money,receipt_money
from sales as t1
left join slip as t2 on t1.sale_slip_no=t2.slip_no

//集計
select t1.sale_slip_no,t2.sales_date,t3.name,ifnull(sum(request_money),0)-ifnull(sum(receipt_money),0) as request_mony
from sales as t1
left join slip as t2 on t1.sale_slip_no = t2.slip_no
left join member as t3 on t2.member_no = t3.member_no
group by sale_slip_no

お礼

yambejpさま

こんばんは。お世話になっております。
実際にSQLまでご提示頂き感謝です。ありがとうございます！

yambejpさまのテーブル構成、拝見いたしました。
確かに、見た目だけではなく、売上内容と伝票データを別にすることで、整った形かと思います。見ていてうなずけました。

yambejpさまご提示の3種のテーブルをみると、顧客情報、伝票情報、売上詳細の3種になっていますが、この売上詳細である、salesを見ると、当初の私の質問時に書いたsalesと比較し、sales_date、member_noを省略し、sales_slip_noで紐付けることが出来る、というのが分かります。

そこで、勉強のつもりで、恥を承知で改めて質問させて下さい。

（教えて君のようで恐縮なのですが）今回の私の質問にある、伝票番号毎の合計（入金も含む）を集計するにはどのようなSQLになるのか、お教え頂くことは可能でしょうか？

今回の伝票番号単位での集計だけであれば、今回私が質問時に提示した構成でも問題はなさそうですが、売上詳細を含まない集計まで視野に入れると、やはりyambejpさまご提示構成の方が適切だろうな...という考えに至っていますが、3つのテーブルを結合しての集計処理に、今回の質問以上に難しく思えてしまって...

恐れ入ります。お忙しい中恐縮ですが引続きアドバイスを頂戴出来れば幸いです。
宜しくお願い申し上げます。

回答No.5

yambejp

＞今回の私の質問にある、伝票番号毎の合計（入金も含む）を集計する

私の認識が間違っていたら申し訳ないですが
それって私の書いた「集計」のSQLだと思うのですがちがいます？

お礼

yambejpさま

すみません！！！！！！

create文とテーブル構成ばかりに気持ちが傾いていたので、一緒にご提示頂いた、一覧、集計のSQLのことすっかり忘れていました！！

本当に申し訳ありません！

で、結果は期待している結果を得ることが出来ました！

yambejpさまのいう正規化を改めて考える切っ掛けになりました。
ユニークな数値が割り当てられたら、テーブルは分割し簡素化した方が宜しいのですね。

今回改めて勉強になりました。
感謝いたします。ありがとうございました！

回答No.3

yambejp

正規化が中途半端

sale_slip_noに対してsales_dateやmember_noがユニークに確定するなら
別テーブルで管理した方がいい
そうすればSQL文も格段に簡略化できます。

お礼

yambejpさま

こんばんは。お世話になっております。

正規化が中途半端、別テーブルにした方が良いとのこと、
貴重なアドバイスをありがとうございます！

質問にある、salesテーブルをもっと分解した方が良いと仰っているのだろうと
認識していますが、改めてテーブル構成を考えているものの、分解しようも、
また同じような構成になってしまう・・と、こんなことを繰り返しています。

yambejpさまが仰るテーブルとはどんな構成でしょうか？
アドバイスを頂けると幸いです。
宜しくお願い申し上げます。

回答No.2

原沢 信道（@nharasawa）

select sale_slip_no,max(sales_date),name1
,sum(coalesce(request_money,0)-coalesce(receipt_maney,0)) as request_money
from sales inner join member on member.member_no=sales.member_no
group by sale_slip_no,name1 order by 1 desc
です。

お礼

nharasawaさま

こんばんは。貴重なアドバイスをありがとうございます。
試したところ、描いていた結果を得ることが出来ました！

はじめてみるSQLでとても勉強になります。ありがとうございます！

回答No.1

Picosoft

手元にMySQLの環境がないのでAccessでしか試していませんが、

SELECT sale_slip_no, sales_date, name1 AS name, SUM(request_money) AS request_money
FROM sales A LEFT JOIN member B ON A.member_no=B.member_no
GROUP BY sale_slip_no, sales_date, name1
ORDER BY sale_slip_no DESC

という感じでどうでしょうか？

お礼

Picosoftさま

こんばんは。質問投稿後、早速のアドバイスをありがとうございました！
アドバイスを頂戴したSQLを試す環境でなかったため、お返事が遅れてしまいました。申し訳ありません。

結果を申し上げると、先ほど試したところ加算すべき金額の合計は出ましたが、入金などの減算すべき金額は反映されていませんでした。

MySQLの環境がない中でのアドバイス、ありがとうございます！