mysql在給定范圍內(nèi)生成一系列日期的最佳方法是什么？我想到的應(yīng)用程序是編寫(xiě)一個(gè)報(bào)表查詢(xún)，為每個(gè)日期返回一行，無(wú)論是否有任何數(shù)據(jù)要報(bào)告。最簡(jiǎn)單的形式：select dates.date, sum(sales.amount)from <series of dates between X and Y> datesleft join sales on date(sales.created) = dates.dategroup by 1我已經(jīng)嘗試創(chuàng)建一個(gè)包含大量日期的表，但這似乎是一個(gè)糟糕的解決方法。
3 回答
不負(fù)相思意
TA貢獻(xiàn)1777條經(jīng)驗(yàn) 獲得超10個(gè)贊
我認(rèn)為擁有一張日歷表是個(gè)好主意; 您可以獲得大量的報(bào)告和查詢(xún)功能，尤其是在填充稀疏數(shù)據(jù)范圍時(shí)。
慕斯王
TA貢獻(xiàn)1864條經(jīng)驗(yàn) 獲得超2個(gè)贊
如果你在像我這樣的情況下禁止創(chuàng)建臨時(shí)表，并且也不允許設(shè)置變量，但是你想生成一個(gè)特定時(shí)期的日期列表，比如當(dāng)前年份做一些聚合，使用這個(gè)
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'
弒天下
TA貢獻(xiàn)1818條經(jīng)驗(yàn) 獲得超8個(gè)贊
您可以使用變量生成日期系列：
Set @i:=0;
SELECT DATE(DATE_ADD(X,
INTERVAL @i:=@i+1 DAY) ) AS datesSeries
FROM yourtable, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y)
;
不知道這是不是你嘗試過(guò):)。
接下來(lái)使用上面生成的查詢(xún)作為表格來(lái)left join：
set @i:=0;
select
d.dates,
sum(s.amount) as TotalAmount
from(
SELECT DATE(DATE_ADD(X,
INTERVAL @i:=@i+1 DAY) ) AS dateSeries
FROM Sales, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y)
) dates d
left join Sales s
on Date(s.Created) = Date(d.dateSeries)
group by 1
;
添加回答
舉報(bào)
0/150
提交
取消