您可以使用经常被称为“日历表”的概念。以下是如何在MySql中创建日历表的好指南:-- create some infrastructureCREATE TABLE ints (i INTEGER);INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- only works for 100 days, add more ints joins for moreSELECT cal.date, tbl.dataFROM (
SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date FROM ints a JOIN ints b ORDER BY a.i * 10 + b.i) cal LEFT JOIN tbl ON cal.date = tbl.dateWHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';
您可能想要创建表cal而不是子选择。