mysqlにてグループ化し合計し、横持ちで取得したい

例えば以下のテーブルから
|ordercode(int)|dat(date)|cat(int)|price(int)|tantou(text)|
|:--|:--:|--:|
|240|2017-05-11|1|12000|鈴木|
|240|2017-05-11|1|3000|鈴木|
|240|2017-05-11|2|8000|鈴木|
|242|2017-05-11|1|12000|田中|
|242|2017-05-11|1|3000|田中|
|242|2017-05-11|1|2000|田中|
|242|2017-05-11|2|10000|田中|
|242|2017-05-11|2|8000|田中|
|243|2017-05-11|1|12000|伊藤|
|243|2017-05-11|2|8000|伊藤|
|243|2017-05-11|2|5000|伊藤|
|243|2017-05-11|2|2000|伊藤|
|243|2017-05-11|2|3000|鈴木|
|243|2017-05-11|3|5000|鈴木|
|243|2017-05-11|3|3000|鈴木|
|245|2017-05-11|1|12000|鈴木|

「ordercode」「dat」「tanotu」でグループ化し
cat毎に合計し
横並びにしたい場合、どのようなsqlを書けばよろしいでしょうか？

|ordercode|dat|tantou|cat1|cat2|cat3|
|:--|:--:|--:|
|240|2017-05-11|鈴木|15000|8000|0|
|242|2017-05-11|田中|17000|18000|0|
|243|2017-05-11|伊藤|12000|15000|0|
|243|2017-05-11|鈴木|0|3000|8000|
|245|2017-05-11|鈴木|12000|0|0|

追記：

上記から発展させたく追記します。

|ordercode(int)|dat(date)|cat(int)|price(int)|tantou(text)|
|:--|:--:|--:|
|240|2017-05-11|1|12000|鈴木|
|240|2017-05-11|1|3000|鈴木|
|240|2017-05-11|2|8000|鈴木|
|240|2017-05-11|3|4000|鈴木|
|242|2017-05-11|1|12000|田中|
|242|2017-05-11|1|3000|田中|
|242|2017-05-11|1|2000|田中|
|242|2017-05-11|3|2000|田中|
|242|2017-05-11|2|10000|田中|
|242|2017-05-11|2|8000|田中|
|243|2017-05-11|1|12000|伊藤|
|243|2017-05-11|2|8000|伊藤|
|243|2017-05-11|2|5000|伊藤|
|243|2017-05-11|2|2000|伊藤|
|243|2017-05-11|1|13000|鈴木|
|243|2017-05-11|2|3000|鈴木|
|243|2017-05-11|3|5000|鈴木|
|243|2017-05-11|3|3000|鈴木|
|245|2017-05-11|1|12000|鈴木|
|245|2017-05-11|3|1000|鈴木|

下記のようにしたい。

条件は
cat3>0である場合
cat2>0であればcat2-cat3を返す
cat2<cat3の場合はcat2を0とし
cat1をcat1-(cat3-cat2)とする

|ordercode|dat|tantou|cat1|cat2|cat3|
|:--|:--:|--:|
|240|2017-05-11|鈴木|15000|4000|4000|
|242|2017-05-11|田中|17000|16000|2000|
|243|2017-05-11|伊藤|12000|15000|0|
|243|2017-05-11|鈴木|8000|0|8000|
|245|2017-05-11|鈴木|11000|0|1000|

行動規範の内容に同意します

回答1件

ベストアンサー

こうしてください

元データ

SQL
1create table tbl(ordercode int,dat date,cat int,price int,tantou varchar(20));
2insert into tbl values
3(240,'2017-05-11',1,12000,'鈴木'),
4(240,'2017-05-11',1,3000,'鈴木'),
5(240,'2017-05-11',2,8000,'鈴木'),
6(242,'2017-05-11',1,12000,'田中'),
7(242,'2017-05-11',1,3000,'田中'),
8(242,'2017-05-11',1,2000,'田中'),
9(242,'2017-05-11',2,10000,'田中'),
10(242,'2017-05-11',2,8000,'田中'),
11(243,'2017-05-11',1,12000,'伊藤'),
12(243,'2017-05-11',2,8000,'伊藤'),
13(243,'2017-05-11',2,5000,'伊藤'),
14(243,'2017-05-11',2,2000,'伊藤'),
15(243,'2017-05-11',2,3000,'鈴木'),
16(243,'2017-05-11',3,5000,'鈴木'),
17(243,'2017-05-11',3,3000,'鈴木'),
18(245,'2017-05-11',1,12000,'鈴木');
19

集計

SQL
1select ordercode,dat,tantou
2,sum(price*(cat=1)) as cat1
3,sum(price*(cat=2)) as cat2
4,sum(price*(cat=3)) as cat3
5from tbl
6group by ordercode,dat,tantou
7

投稿2017/05/22 08:31

yambejp

総合スコア117715

yambejp

2017/05/22 08:47

ちなみにtantouのように日本語入力されるようなブレがでそうなものは userid的なもので正規化したほうがSQL的な処理になります

u-sukesan

2017/05/22 13:12

yambejp様ありがとうございます。希望の結果を得ることができました。ここから少し発展させたいのですが追記しましたご指摘通りtantouは質問をわかりやすくするために日本語にしてあります。こちらでは数値を入れてありますので大丈夫です^^

yambejp

2017/05/22 13:53 編集

なんとなくわかりました select ordercode,dat,tantou ,cat1-(case when cat2<=cat3 then cat3-cat2 else 0 end) as cat1 ,case when cat2>cat3 then cat2-cat3 else 0 end as cat2 ,cat3 from ( select ordercode,dat,tantou ,sum(price*(cat=1)) as cat1 ,sum(price*(cat=2)) as cat2 ,sum(price*(cat=3)) as cat3 from tbl group by ordercode,dat,tantou ) as sub;

u-sukesan

2017/05/25 04:46

いただきました内容をヒントに解決できました！重ね重ねありがとうございました。また機会がありましたらよろしくお願いいたします

行動規範の内容に同意します