SQL空值未显示在结果中

Radium 发表于 Dev

镭

我在获取SQL查询的空值时遇到麻烦。这是问题的描述：

每周总收入。客人离开时从他们那里收钱。对于2016年11月和2016年12月的每个星期四，请显示从上一个星期五到该天（包括该日）收集的总金额。

这是我编写的代码，应该返回从星期四到上周五的每周收入，我得到的答案在一定程度上是正确的，因为正确显示了有收入的星期，而没有显示没有收入的星期。我尝试添加IFNULL子句，但这仍然不能解决问题。

SELECT DATE_ADD(MAKEDATE(2016, 7), INTERVAL WEEK(DATE_ADD(calendar.i, INTERVAL booking.nights - 5 DAY), 0) WEEK) AS Thursday, IFNULL(SUM(booking.nights * rate.amount) + SUM(e.amount),0) AS weekly_ncome
FROM booking
RIGHT OUTER
JOIN calendar ON booking.booking_date = calendar.i
JOIN rate ON (booking.occupants = rate.occupancy AND booking.room_type_requested = rate.room_type)
LEFT JOIN (
    SELECT booking_id, IFNULL(SUM(amount),0) AS amount
    FROM extra
    GROUP BY booking_id
) AS e ON (e.booking_id = booking.booking_id)
GROUP BY Thursday;

作为参考，这是一个在SQLzoo Guesthouse部分的问题15中找到的问题。这是预期的结果：

+------------+---------------+
| Thursday   | weekly_income |
+------------+---------------+
| 2016-11-03 |          0.00 |
| 2016-11-10 |      12608.94 |
| 2016-11-17 |      13552.56 |
| 2016-11-24 |      12929.69 |
| 2016-12-01 |      11685.14 |
| 2016-12-08 |      13093.79 |
| 2016-12-15 |       8975.87 |
| 2016-12-22 |       1395.77 |
| 2016-12-29 |          0.00 |
| 2017-01-05 |          0.00 |
+------------+---------------+

我和上面的一样，但是每周收入为0的那些没有出现。

数据库图

萨尔曼A

这是获取2016年11月和2016年12月星期四的一种方法：

    SELECT i AS thursday, i - INTERVAL 6 DAY AS friday
    FROM calendar
    WHERE i >= '2016-11-01' AND i - INTERVAL 6 DAY <= '2016-12-31' AND DAYOFWEEK(i) = 5

刚加入此数据，请确保您以结帐日期（booking_date + nights days）加入：

SELECT
    thursday, SUM(
        COALESCE(booking.nights * rate.amount, 0) +
        COALESCE(extras.total, 0)
    ) AS weekly_income
FROM (
    SELECT i AS thursday, i - INTERVAL 6 DAY AS friday
    FROM calendar
    WHERE i >= '2016-11-01' AND i - INTERVAL 6 DAY <= '2016-12-31' AND DAYOFWEEK(i) = 5
) AS thursdays
LEFT JOIN (
    booking
    INNER JOIN rate ON booking.occupants = rate.occupancy AND booking.room_type_requested = rate.room_type
    LEFT JOIN (
        SELECT booking_id, SUM(amount) AS total
        FROM extra
        GROUP BY booking_id
    ) AS extras ON booking.booking_id = extras.booking_id
) ON booking.booking_date + INTERVAL booking.nights DAY BETWEEN friday AND thursday
GROUP BY thursday

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。