MySQL Pad 空月汇总报告。 (那里的 3/4 路)

Question

这个问题来自:MySQL Number of Days inside a DateRange, inside a month (Booking Table)

我有一个包含以下数据的表:

CREATE TABLE IF NOT EXISTS `bookingdata` (
`idBookingData` int(11) NOT NULL AUTO_INCREMENT,
`PropertyID` int(10) NOT NULL,
`Checkin` date DEFAULT NULL,
`Checkout` date DEFAULT NULL,
`Rent` decimal(10,2) DEFAULT NULL,
`BookingID` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookingData`),
UNIQUE KEY `idBookingData_UNIQUE` (`idBookingData`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10472 ;

INSERT INTO `bookingdata` (`idBookingData`, `PropertyID`, `Checkin`, `Checkout`, `Rent`, `BookingID`) VALUES
(5148, 2, '2011-07-02', '2011-07-05', 1105.00, 10612),
(5149, 2, '2011-07-05', '2011-07-13', 2155.00, 10184),
(5151, 2, '2011-07-14', '2011-07-17', 1105.00, 11102),
(5153, 2, '2011-07-22', '2011-07-24', 930.00, 14256),
(5154, 2, '2011-07-24', '2011-08-04', 1832.73, 9216),
(5907, 2, '2011-07-24', '2011-08-04', 687.27, 9216),
(5910, 2, '2011-08-11', '2011-08-14', 1140.00, 13633),
(5911, 2, '2011-08-15', '2011-08-16', 380.00, 17770),
(5915, 2, '2011-08-25', '2011-08-29', 1350.00, 17719),
(5916, 2, '2011-08-30', '2011-09-01', 740.00, 16813);

然后我有这个 SQL 查询:

SELECT   
    YEAR(month.d),
    MONTHNAME(month.d),
    IFNULL(SUM(
           DATEDIFF(LEAST(Checkout, LAST_DAY(month.d)), GREATEST(Checkin, month.d))
    ),'0') AS Days
FROM    bookingdata
RIGHT JOIN (
        SELECT 20110101 AS d
        UNION ALL SELECT 20110201 UNION ALL SELECT 20110301
        UNION ALL SELECT 20110401 UNION ALL SELECT 20110501
        UNION ALL SELECT 20110601 UNION ALL SELECT 20110701
        UNION ALL SELECT 20110801 UNION ALL SELECT 20110901
        UNION ALL SELECT 20111001 UNION ALL SELECT 20111101
        UNION ALL SELECT 20111201
        ) AS month ON (
            Checkin BETWEEN month.d AND LAST_DAY(month.d) OR month.d BETWEEN Checkin AND Checkout
        )
WHERE  PropertyID = "Mount Olive" 
GROUP BY month.d

输出如下:

+-------------+-------------------+------+
|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
+-------------+-------------------+------+
|2011         | July              | 30   |
|2011         | August            | 15   |
|2011         | September         |  0   |
+-------------+-------------------+------+

尽管我希望它创建以下内容(使用填充的空值):

+-------------+-------------------+------+
|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
+-------------+-------------------+------+
|2011         | January           | 0    |
|2011         | Feburary          | 0    |
|2011         | March             | 0    |
|2011         | April             | 0    |
|2011         | May               | 0    |
|2011         | June              | 0    |
|2011         | July              | 30   |
|2011         | August            | 15   |
|2011         | September         |  0   |
|2011         | October           | 0    |
|2011         | Novemeber         | 0    |
|2011         | December          | 0    |
+-------------+-------------------+------+

我需要添加什么才能让它做到这一点？在 SQL Fiddle 上:http://sqlfiddle.com/#!2/c6bdc/2/0

Answer 1

它连接正确 - 正确的连接是完全正确的。问题是您正在过滤掉结果。在右侧(月份)与左侧(预订)不匹配的行中，左侧的字段(包括 propertyId)将为 NULL。 NULL 不是“2”或“Mount Olive”，因此排除了该行。

检查 PropertyId 时，使用 IFNULL/COALESCE:

WHERE  COALESCE(PropertyID, "Mount Olive") = "Mount Olive" 

或者使条件复合: WHERE PropertyID 为 NULL 或 PropertyID = "Mount Olive"

fiddle : http://sqlfiddle.com/#!2/c6bdc/4/0