php、mysqlを使用し配列の質問です。

Question

zaiseki、idouのデータを利用し下記の出力の様に月別に在籍数を表示したい。

やりたいこと
出力
bumon ：aa部門
１月　２月
入社　1 0
退社 0 1
在籍 11 10

bumon ：bb部門
１月　２月
入社　0 0
退社 0 2
在籍 20 18

bumon ：cc部門
１月　２月
入社　0 　3
退社 0 0
在籍 10 13

このように部門別、月別に在籍数を表示させたい。

利用するデータは下記の2つ
zaiseki：201712末の部門ごとの在籍数
Idou：2018の入退社のデータ

‘zaikeki’ (neme’,’zaiseki’)
‘aa部門’,’10’
‘bb部門’,’20’
‘cc部門’,’10’

‘Idou’(‘bumon’,’sutatus’,’date’
‘aa部門’,’入社’,’2018-01-01’
‘bb部門’,退社’,’2018-02-01’
‘bb部門’,退社’,’2018-02-01’
‘cc部門’,’入社’,’2018-02-01’
‘cc部門’,’入社’,’2018-02-01’
‘cc部門’,’入社’,’2018-02-01’
‘aa部門’,’退社’,’2018-02-20’

SELECT bumon AS ‘部門’,
COUNT(CASE WHEN date_format(date,’%Y%m’)=‘201801’ THEN 1 ELSE null END) as ’01月’,
COUNT(CASE WHEN date_format(date,’%Y%m’)=‘201802’ THEN 1 ELSE null END) as ’02月’,
COUNT(CASE WHEN date_format(date,’%Y%m’)=‘201803’ THEN 1 ELSE null END) as ’03月’,
〜
COUNT(CASE WHEN date_format(date,’%Y%m’)=‘201812’ THEN 1 ELSE null END) as ’12月’
FORM idou
group by name, status

上記のSQLでCOUNTを取り’idou’のデータを月毎に「入社」「退社」に集計することはできたのですが、
zaisekiのデータを含めての計算式をどの様に書いていいのか検討がつきません。

お手数おかけしますが上記の内容をお教授お願いします。

Comments

[退会済みユーザー]

タイプミスが多いこと、識別子として同一としてみていいのかどうかあやふやなので、この状況だと回答しづらいです。CREATE TABLE文やINSERT INTO文などで第三者も再現できるようなサンプルデータ提供が望ましいです。

[退会済みユーザー]

idouテーブルのdateはDATE型とかTIMESTAMP型ですか？ 日付時刻データはそれにふさわしいデータ型を使ってください。年や月の抽出が楽になり、値の比較もしやすくなります。

[yambejp]

この構成だと相当むりがあります。テーブルの仕様は変更可能ですか？

[退会済みユーザー]

返信遅れてすみません
テーブルの変更可能です。

タイプミスについても申し訳ございません。

[退会済みユーザー]

テーブルの変更可能です。

Answer 1

とりあえず最大限オリジナルに近づけました

• 元データ

create table bumon (bid int primary key,bname varchar(20),zaiseki int);
insert into bumon values
(1,'aa部門',10),
(2,'bb部門',20),
(3,'cc部門',10);

create table idou(bname varchar(20),status varchar(20),d date);
insert into idou values
('aa部門','入社','2018-01-01'),
('bb部門','退社','2018-02-01'),
('bb部門','退社','2018-02-01'),
('cc部門','入社','2018-02-01'),
('cc部門','入社','2018-02-01'),
('cc部門','入社','2018-02-01'),
('aa部門','退社','2018-02-20');	

• view

再利用性を考えビューをつくっておきます

create view list as 
select bname,date_format(d,'%Y%m') as d,status,sum(case status when '入社' then 1 when '退社' then -1 end) as num from idou group by bname,status,d
union all select bname,'201801','在籍',zaiseki from bumon;

• 結果

select * from list;

bname	d	status	num
aa部門	201802	退社	-1
aa部門	201801	入社	1
bb部門	201802	退社	-2
cc部門	201802	入社	3
aa部門	201801	在籍	10
bb部門	201801	在籍	20
cc部門	201801	在籍	10

• 入退社と在庫を別々に拾ってunionする

select t3.bname,t3.status
,coalesce(sum((d2='201801')*abs(num)),0) as `201801`
,coalesce(sum((d2='201802')*abs(num)),0) as `201802`
 from (
select t1.bname,t2.status from 
(select distinct bname from list) as t1,
(select distinct status from list where status in ('入社','退社')) as t2
) as t3 left join list as t4 on t3.bname=t4.bname and t3.status=t4.status 
group by bname,status

union all
select t3.bname,'在籍'
,sum((d2 between '201801' and '201801')*num)
,sum((d2 between '201801' and '201802')*num)
 from (
select t1.bname,t2.status from 
(select distinct bname from list) as t1,
(select distinct status from list) as t2
) as t3 left join list as t4 on t3.bname=t4.bname and t3.status=t4.status 
group by bname
order by bname,case status when '入社' then 1 when '退社' then 2 when '在籍' then 3 end 

• 結果

bname	status	201801	201802
aa部門	入社	1	0
aa部門	退社	0	1
aa部門	在籍	11	10
bb部門	入社	0	0
bb部門	退社	0	2
bb部門	在籍	20	18
cc部門	入社	0	3
cc部門	退社	0	0
cc部門	在籍	10	13

Comments

[退会済みユーザー]

問題なく対応できました。
ありがとうございました。