mysql在給定范圍內(nèi)生成一系列日期的最佳方法是什么？我想到的應用程序是編寫一個報表查詢，為每個日期返回一行，無論是否有任何數(shù)據(jù)要報告。最簡單的形式：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)建一個包含大量日期的表，但這似乎是一個糟糕的解決方法。
3 回答
慕斯王
TA貢獻1864條經(jīng)驗 獲得超2個贊
如果你在像我這樣的情況下禁止創(chuàng)建臨時表，并且也不允許設置變量，但是你想生成一個特定時期的日期列表，比如當前年份做一些聚合，使用這個
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貢獻1818條經(jīng)驗 獲得超8個贊
您可以使用變量生成日期系列：
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)
;
不知道這是不是你嘗試過:)。
接下來使用上面生成的查詢作為表格來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
;
添加回答
舉報
0/150
提交
取消