問(wèn)題描述
我想運(yùn)行一個(gè)查詢(xún)
select ... 作為`date` 介于'2010-01-20' 和'2010-01-24' 之間的天數(shù)并返回如下數(shù)據(jù):
<前>天----------2010-01-202010-01-212010-01-222010-01-232010-01-24此解決方案使用無(wú)循環(huán)、過(guò)程或臨時(shí)表.子查詢(xún)生成過(guò)去 10,000 天的日期，并且可以擴(kuò)展到任意遠(yuǎn)的后退或前進(jìn).
選擇一個(gè).Date從 (選擇 curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY 作為日期from(選擇0作為聯(lián)合全選1聯(lián)合全選2聯(lián)合全選3聯(lián)合全選4聯(lián)合全選5聯(lián)合全選6聯(lián)合全選7聯(lián)合全選8聯(lián)合全選9)作為交叉聯(lián)接(選擇 0 作為聯(lián)合全選 1 聯(lián)合全選 2 聯(lián)合全選 3 聯(lián)合全選 4 聯(lián)合全選 5 聯(lián)合全選 6 聯(lián)合全選 7 聯(lián)合全選 8 聯(lián)合全選 9) as b交叉聯(lián)接(選擇 0 作為聯(lián)合全選 1 聯(lián)合全選 2 聯(lián)合全選 3 聯(lián)合全選 4 聯(lián)合全選 5 聯(lián)合全選 6 聯(lián)合全選 7 聯(lián)合全選 8 聯(lián)合全選 9) as c交叉聯(lián)接(選擇 0 作為聯(lián)合全選 1 聯(lián)合全選 2 聯(lián)合全選 3 聯(lián)合全選 4 聯(lián)合全選 5 聯(lián)合全選 6 聯(lián)合全選 7 聯(lián)合全選 8 聯(lián)合全選 9) as d) 一種其中 a.2010-01-20"和2010-01-24"之間的日期輸出:
日期----------2010-01-242010-01-232010-01-222010-01-212010-01-20性能說(shuō)明
測(cè)試一下這里，性能出奇的好:以上查詢(xún)需要 0.0009 秒.
如果我們擴(kuò)展子查詢(xún)以生成大約.100,000 個(gè)數(shù)字(因此大約 274 年的日期)，它在 0.0458 秒內(nèi)運(yùn)行.
順便說(shuō)一下，這是一種非常便攜的技術(shù)，只需稍作調(diào)整即可適用于大多數(shù)數(shù)據(jù)庫(kù).
返回 1,000 天的 SQL Fiddle 示例
I would like to run a query like
select ... as days where `date` is between '2010-01-20' and '2010-01-24'
And return data like:
days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24
This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'
Output:
Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20
Notes on Performance
Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.
If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.
Incidentally, this is a very portable technique that works with most databases with minor adjustments.
SQL Fiddle example returning 1,000 days
這篇關(guān)于從日期范圍生成天數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！