回答編集履歴

6 修正版

yambejp

yambejp score 67311

2017/09/04 20:24  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei
```
上記サンプルの2と3をまとめて別のコード=99として集計します
```SQL
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei2
```
# ついでに
会社名と属性を別テーブルで用意してやるとこう
```SQL
create table t_kaisha(kid int unique key,kname varchar(20));
insert into t_kaisha values(1,'F社'),(2,'R社'),(999,'全社');
create table t_zokusei(zid int unique key,zname varchar(20));
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他'),(999,'合計');
```
リレーションしてこう(小計欄集計をつけときました)
```SQL
select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m5
,t1.m6
,t1.m7
from (
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl,t_zokusei
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;
```  
 
 
# 修正版?  
```  
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);  
insert into tbl values  
(1,1,'2017-06-01',100),  
(1,2,'2017-06-01',100),  
(1,3,'2017-06-01',100),  
(1,4,'2017-06-01',100),  
(2,1,'2017-06-01',100),  
(2,2,'2017-06-01',100),  
(2,3,'2017-06-01',100),  
(2,4,'2017-06-01',100),  
(3,1,'2017-06-01',100),  
(3,2,'2017-06-01',100),  
(3,3,'2017-06-01',100),  
(3,4,'2017-06-01',100);  
create table t_kaisha(kid int unique key,kname varchar(20));  
insert into t_kaisha values(1,'F社'),(2,'R社'),(3,'C社'),(999,'合計');  
create table t_zokusei(zid int unique key,zname varchar(20));  
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'国内通販'),(4,'海外通販'),(999,'合計');  
select concat(kname,' - ',zname) as kz  
,coalesce(kaisha,999) as kaisha  
,coalesce(zokusei2,999) as zokusei2  
,t1.m6  
from (  
select kaisha,case when zokusei in (1,2) then 1 else zokusei end as zokusei2  
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6  
from tbl  
where date_format(hiduke,'%Y')='2017'  
group by kaisha asc ,zokusei2 asc with rollup  
) as t1  
left join t_kaisha on coalesce(kaisha,999)=kid  
left join t_zokusei on coalesce(zokusei2,999)=zid  
order by kaisha,zokusei2;  
```
5 小計

yambejp

yambejp score 67311

2017/09/01 20:54  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei
```
上記サンプルの2と3をまとめて別のコード=99として集計します
```SQL
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei2
```
# ついでに
会社名と属性を別テーブルで用意してやるとこう
```SQL
create table t_kaisha(kid int unique key,kname varchar(20));
insert into t_kaisha values(1,'F社'),(2,'R社');
insert into t_kaisha values(1,'F社'),(2,'R社'),(999,'全社');
create table t_zokusei(zid int unique key,zname varchar(20));
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他');
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他'),(999,'合計');
```
リレーションしてこう
リレーションしてこう(小計欄集計をつけときました)
```SQL
select concat(kname,' - ',zname) as kz,t1.*
select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m5
,t1.m6
,t1.m7
from (
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl,t_zokusei
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei2
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on kaisha=kid
left join t_zokusei on zokusei2=zid
order by kaisha,zokusei2
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;
```
4 ついでに

yambejp

yambejp score 67311

2017/09/01 20:27  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei
```
上記サンプルの2と3をまとめて別のコード=99として集計します
```SQL
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei2
```  
 
# ついでに  
会社名と属性を別テーブルで用意してやるとこう  
```SQL  
create table t_kaisha(kid int unique key,kname varchar(20));  
insert into t_kaisha values(1,'F社'),(2,'R社');  
create table t_zokusei(zid int unique key,zname varchar(20));  
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他');  
```  
リレーションしてこう  
```SQL  
select concat(kname,' - ',zname) as kz,t1.*  
from (  
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2  
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5  
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6  
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7  
from tbl,t_zokusei  
where date_format(hiduke,'%Y')='2017'  
group by kaisha,zokusei2  
) as t1  
left join t_kaisha on kaisha=kid  
left join t_zokusei on zokusei2=zid  
order by kaisha,zokusei2  
```
3 zokuseiまとめ

yambejp

yambejp score 67311

2017/09/01 20:07  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei
```  
上記サンプルの2と3をまとめて別のコード=99として集計します  
```SQL  
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2  
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5  
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6  
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7  
from tbl  
where date_format(hiduke,'%Y')='2017'  
group by kaisha,zokusei2  
```
2 追加情報

yambejp

yambejp score 67311

2017/09/01 19:58  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'  
group by kaisha,zokusei
```
1 sample

yambejp

yambejp score 67311

2017/09/01 19:56  投稿

全体的に無駄が多いように見えます
GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは?
で集計すればよいのでは?
# sample
まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします
```SQL
create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);
```
集計します
```
select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
group by kaisha,zokusei
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る