売上管理

Question

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

以下にテーブルの構成および得たい結果を書き残しますので、ご教示頂けた幸いです。（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になるのかご教示いただければ幸いです。

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

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

yambejp · Accepted Answer

#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 · Answer

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

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

yambejp · Answer

正規化が中途半端

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

nharasawa · Answer

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
です。

Picosoft · Answer

手元に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

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

売上管理

#3です

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

正規化が中途半端

select sale_slip_no,max(sales_date),name1

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング