Mysql
獲取目前月份的所有日期
我在 MySQL 中有一個查詢,它通過獲取當月的所有記錄對我很有幫助;
SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH);
上面的查詢效果很好。所以如果這個月我們只有兩個記錄和 28 天,它只會帶來兩個記錄。
date_field | val ======================== 2015-02-08 | 567 2015-02-09 | 345
但我希望返回的記錄數與當月的天數完全相同。如果當月有 28 天並且只有兩條記錄,它應該帶來;
date_field | val ======================== 2015-02-01 | 0 2015-02-02 | 0 2015-02-03 | 0 2015-02-04 | 0 2015-02-05 | 0 2015-02-06 | 0 2015-02-07 | 0 2015-02-08 | 567 2015-02-09 | 345 2015-02-10 | 0 2015-02-11 | 0 2015-02-12 | 0 2015-02-13 | 0 2015-02-14 | 0 2015-02-15 | 0 2015-02-16 | 0 2015-02-17 | 0 2015-02-18 | 0 2015-02-19 | 0 2015-02-20 | 0 2015-02-21 | 0 2015-02-22 | 0 2015-02-23 | 0 2015-02-24 | 0 2015-02-25 | 0 2015-02-26 | 0 2015-02-27 | 0 2015-02-28 | 0
如何修改我的查詢以實現上述結果?
首先,該條件
WHERE date_field >= (CURDATE()-INTERVAL 1 MONTH)
不會將您的結果限制在當月。它將獲取從 30-31 天前到目前日期的所有日期(如果表中有包含未來日期的行,則到未來)。它應該是:
WHERE date_field >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AND date_field < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
現在,對於主要問題,要創建 28-31 個日期,即使表中沒有所有日期的行,您也可以使用
Calendar
表(包含所有日期,例如 1900 年到 2200 年)或即時創建它們,使用這樣的東西(該days
表可以是臨時表,或者您甚至可以將其設為派生表,查詢比這個更複雜一些):CREATE TABLE days ( d INT NOT NULL PRIMARY KEY ) ; INSERT INTO days VALUES (0), (1), (2), .... ..., (28), (29), (30) ; SELECT cal.my_date AS date_field, COALESCE(t.val, 0) AS val FROM ( SELECT s.start_date + INTERVAL (days.d) DAY AS my_date FROM ( SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS start_date, LAST_DAY(CURRENT_DATE) AS end_date ) AS s JOIN days ON days.d <= DATEDIFF(s.end_date, s.start_date) ) AS cal LEFT JOIN my_table AS t ON t.date_field >= cal.my_date AND t.date_field < cal.my_date + INTERVAL 1 DAY ;
以上應該適用於任何類型的
date_field
列(日期、日期時間、時間戳)。如果該date_field
列的類型為DATE
,則最後一個連接可以簡化為:LEFT JOIN my_table AS t ON t.date_field = cal.my_date ;