MySQL:从表中获取未包含的日期

Question

请引用此sqlfiddle 。在该表中，缺少从 2014-09-21 到 2014-09-30 的数据。

第二条sql就是我想要的。如果分配之间没有差异，那么结果就可以。但不同的是，我想要 0 作为输出。因为有些日期没有分配。

请帮我解决这个问题。

谢谢。

Answer 1

尝试这样

SELECT room_id, from_date, to_date, MIN(rooms) rooms FROM(

SELECT DISTINCT room_id, from_date, to_date, SUM(IFNULL(rooms,0)) rooms 
FROM room_allotment 
WHERE (from_date BETWEEN '2014-09-21' AND '2014-09-29'  and
     to_date BETWEEN '2014-09-21' AND '2014-09-29' ) AND room_id = '1'
GROUP BY room_id 
UNION ALL
SELECT DISTINCT room_id, from_date, to_date, SUM(IFNULL(rooms,0)) rooms 
FROM room_allotment 
WHERE (from_date BETWEEN '2014-09-21' AND '2014-09-29'  and
     to_date BETWEEN '2014-09-21' AND '2014-09-29' ) AND room_id = '1'
GROUP BY room_id
)r
GROUP BY room_id

SQL FIDDLE