回答編集履歴
1
予算
answer
CHANGED
|
@@ -17,4 +17,65 @@
|
|
|
17
17
|
```
|
|
18
18
|
予算のnullが気になる場合は
|
|
19
19
|
,coalesce(sum((bt.month='06')*budget),0) as m6b
|
|
20
|
-
としてもいいかもしれません。
|
|
20
|
+
としてもいいかもしれません。
|
|
21
|
+
|
|
22
|
+
# 予算分調整
|
|
23
|
+
少し汎用性をあげました
|
|
24
|
+
```
|
|
25
|
+
select
|
|
26
|
+
kaisha,zokusei2
|
|
27
|
+
,sum(m6*(t2.m=5)) as m5
|
|
28
|
+
,coalesce(sum(budget*(bt.month=5)),0) as m5b
|
|
29
|
+
,sum(m6*(t2.m=6)) as m6
|
|
30
|
+
,coalesce(sum(budget*(bt.month=6)),0) as m6b
|
|
31
|
+
from (
|
|
32
|
+
select
|
|
33
|
+
kaisha
|
|
34
|
+
,case when zokusei in (1,2) then 1 else zokusei end as zokusei2
|
|
35
|
+
,date_format(hiduke,'%Y') as y
|
|
36
|
+
,date_format(hiduke,'%m') as m
|
|
37
|
+
,sum(price) as m6
|
|
38
|
+
from tbl as t1
|
|
39
|
+
group by kaisha ,zokusei2 ,y,m
|
|
40
|
+
) as t2
|
|
41
|
+
left join budget_target as bt on bt.kid=t2.kaisha and bt.zid=t2.zokusei2 and bt.year=t2.y and bt.month=t2.m
|
|
42
|
+
where t2.y='2017'
|
|
43
|
+
group by kaisha asc,zokusei2 asc with rollup
|
|
44
|
+
```
|
|
45
|
+
これをt_kaisha,t_zaikoをかぶせて
|
|
46
|
+
|
|
47
|
+
```
|
|
48
|
+
select
|
|
49
|
+
coalesce(kaisha,999) as kaisha,
|
|
50
|
+
coalesce(zokusei2,999) as zokusei2,
|
|
51
|
+
concat(tk.kname,' - ',tz.zname) as kz,
|
|
52
|
+
m5,
|
|
53
|
+
m5b,
|
|
54
|
+
m6,
|
|
55
|
+
m6b
|
|
56
|
+
from
|
|
57
|
+
(select
|
|
58
|
+
kaisha,zokusei2
|
|
59
|
+
,sum(m6*(t2.m=5)) as m5
|
|
60
|
+
,coalesce(sum(budget*(bt.month=5)),0) as m5b
|
|
61
|
+
,sum(m6*(t2.m=6)) as m6
|
|
62
|
+
,coalesce(sum(budget*(bt.month=6)),0) as m6b
|
|
63
|
+
from (
|
|
64
|
+
select
|
|
65
|
+
kaisha
|
|
66
|
+
,case when zokusei in (1,2) then 1 else zokusei end as zokusei2
|
|
67
|
+
,date_format(hiduke,'%Y') as y
|
|
68
|
+
,date_format(hiduke,'%m') as m
|
|
69
|
+
,sum(price) as m6
|
|
70
|
+
from tbl as t1
|
|
71
|
+
group by kaisha ,zokusei2 ,y,m
|
|
72
|
+
) as t2
|
|
73
|
+
left join budget_target as bt on bt.kid=t2.kaisha and bt.zid=t2.zokusei2 and bt.year=t2.y and bt.month=t2.m
|
|
74
|
+
where t2.y='2017'
|
|
75
|
+
group by kaisha asc,zokusei2 asc with rollup
|
|
76
|
+
) as t3
|
|
77
|
+
left join t_kaisha as tk on coalesce(kaisha,999)=tk.kid
|
|
78
|
+
left join t_zokusei as tz on coalesce(zokusei2,999)=tz.zid
|
|
79
|
+
order by kaisha,zokusei2
|
|
80
|
+
|
|
81
|
+
```
|