day.sql
4.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
select
b.id,
b.model,
b.price,
IFNULL(sum(`2019-04-01`), 0) AS '2019-04-01',
IFNULL(sum(`2019-04-02`), 0) AS '2019-04-02',
IFNULL(sum(`2019-04-03`), 0) AS '2019-04-03',
IFNULL(sum(`2019-04-04`), 0) AS '2019-04-04',
IFNULL(sum(`2019-04-05`), 0) AS '2019-04-05',
IFNULL(sum(`2019-04-06`), 0) AS '2019-04-06',
IFNULL(sum(`2019-04-07`), 0) AS '2019-04-07',
IFNULL(sum(`2019-04-08`), 0) AS '2019-04-08',
IFNULL(sum(`2019-04-09`), 0) AS '2019-04-09',
IFNULL(sum(`2019-04-10`), 0) AS '2019-04-10',
IFNULL(sum(`2019-04-11`), 0) AS '2019-04-11',
IFNULL(sum(`2019-04-12`), 0) AS '2019-04-12',
IFNULL(sum(`2019-04-13`), 0) AS '2019-04-13',
IFNULL(sum(`2019-04-14`), 0) AS '2019-04-14',
IFNULL(sum(`2019-04-15`), 0) AS '2019-04-15',
IFNULL(sum(`2019-04-16`), 0) AS '2019-04-16',
IFNULL(sum(`2019-04-17`), 0) AS '2019-04-17',
IFNULL(sum(`2019-04-18`), 0) AS '2019-04-18',
IFNULL(sum(`2019-04-19`), 0) AS '2019-04-19',
IFNULL(sum(`2019-04-20`), 0) AS '2019-04-20',
IFNULL(sum(`2019-04-21`), 0) AS '2019-04-21',
IFNULL(sum(`2019-04-22`), 0) AS '2019-04-22',
IFNULL(sum(`2019-04-23`), 0) AS '2019-04-23',
IFNULL(sum(`2019-04-24`), 0) AS '2019-04-24',
IFNULL(sum(`2019-04-25`), 0) AS '2019-04-25',
IFNULL(sum(`2019-04-26`), 0) AS '2019-04-26',
IFNULL(sum(`2019-04-27`), 0) AS '2019-04-27',
IFNULL(sum(`2019-04-28`), 0) AS '2019-04-28',
IFNULL(sum(`2019-04-29`), 0) AS '2019-04-29',
IFNULL(sum(`2019-04-30`), 0) AS '2019-04-30'
from
sknprice b
left join
(
select
productId,
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-01' then b.price else 0 end as '2019-04-01',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-02' then b.price else 0 end as '2019-04-02',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-03' then b.price else 0 end as '2019-04-03',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-04' then b.price else 0 end as '2019-04-04',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-05' then b.price else 0 end as '2019-04-05',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-06' then b.price else 0 end as '2019-04-06',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-07' then b.price else 0 end as '2019-04-07',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-08' then b.price else 0 end as '2019-04-08',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-09' then b.price else 0 end as '2019-04-09',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-10' then b.price else 0 end as '2019-04-10',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-11' then b.price else 0 end as '2019-04-11',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-12' then b.price else 0 end as '2019-04-12',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-13' then b.price else 0 end as '2019-04-13',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-14' then b.price else 0 end as '2019-04-14',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-15' then b.price else 0 end as '2019-04-15',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-16' then b.price else 0 end as '2019-04-16',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-17' then b.price else 0 end as '2019-04-17',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-18' then b.price else 0 end as '2019-04-18',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-19' then b.price else 0 end as '2019-04-19',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-20' then b.price else 0 end as '2019-04-20',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-21' then b.price else 0 end as '2019-04-21',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-22' then b.price else 0 end as '2019-04-22',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-23' then b.price else 0 end as '2019-04-23',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-24' then b.price else 0 end as '2019-04-24',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-25' then b.price else 0 end as '2019-04-25',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-26' then b.price else 0 end as '2019-04-26',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-27' then b.price else 0 end as '2019-04-27',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-28' then b.price else 0 end as '2019-04-28',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-29' then b.price else 0 end as '2019-04-29',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-04-30' then b.price else 0 end as '2019-04-30'
from
buyers_2019_04 a left join sknprice b on a.productId = b.id
where
time >= '2019-04-01' and time < '2019-05-01'
) A on A.productId = b.id
group by b.id,b.model,b.price