Вам понадобится создать виртуальную (или физическую) таблицу, содержащую каждую дату в диапазоне.
Это можно сделать следующим образом, используя таблицу последовательностей.
SELECT mintime + INTERVAL seq.seq DAY AS orderdate
FROM (
SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
CURDATE() AS maxtime
FROM obs
) AS minmax
JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)
Затем вы присоединяете эту виртуальную таблицу к вашему запросу следующим образом.
SELECT IFNULL(orders.Norders,0) AS Norders, /* show zero instead of null*/
DATE_FORMAT(alldates.orderdate, "%M %e") as sdate
FROM (
SELECT mintime + INTERVAL seq.seq DAY AS orderdate
FROM (
SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
CURDATE() AS maxtime
FROM obs
) AS minmax
JOIN seq_0_to_999999 AS seq
ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)
) AS alldates
LEFT JOIN (
SELECT COUNT(*) as Norders, DATE(date) AS orderdate
FROM ORDERS
WHERE date <= NOW()
AND date >= NOW() - INTERVAL 1 MONTH
GROUP BY DAY(date)
) AS orders ON alldates.orderdate = orders.orderdate
ORDER BY alldates.orderdate ASC
Обратите внимание, что вам нужно LEFT JOIN
, чтобы строки в вашем результирующем наборе результатов сохранялись, даже если в вашей таблице ORDERS
нет данных.
Где вы можете получить эту таблицу последовательности seq_0_to_999999
]? Вы можете сделать это следующим образом.
DROP TABLE IF EXISTS seq_0_to_9;
CREATE TABLE seq_0_to_9 AS
SELECT 0 AS seq 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;
DROP VIEW IF EXISTS seq_0_to_999;
CREATE VIEW seq_0_to_999 AS (
SELECT (a.seq + 10 * (b.seq + 10 * c.seq)) AS seq
FROM seq_0_to_9 a
JOIN seq_0_to_9 b
JOIN seq_0_to_9 c
);
DROP VIEW IF EXISTS seq_0_to_999999;
CREATE VIEW seq_0_to_999999 AS (
SELECT (a.seq + (1000 * b.seq)) AS seq
FROM seq_0_to_999 a
JOIN seq_0_to_999 b
);
Подробнее об этом можно узнать на http://www.plumislandmedia.net/mysql/filling-missing-data- sequence-cardinal-integers /
Если вы используете MariaDB версии 10+, эти таблицы последовательности встроены.