mysql - 子查询中的MySQL count()

我正在尝试通过以下查询计算未来7天的预订数量。

select calendarDate,
   (
    select COUNT(*)
    FROM isBooked INNER JOIN booking
    ON isbooked.BookingID = booking.bookingID
    where specificday between booking.startDate and booking.endDate
    )
from calendar as specificday
where calendardate between  '2015-08-23' and DATE_ADD('2015-08-23', INTERVAL 6 DAY);

我已经使用了允许使用“截至特定日期”的SQL Server，但是MySQL不允许，我将如何在mysql中重写查询。

最佳答案

specificday引用表，而不是列。您需要WHERE子句的列名：

select c.calendarDate,
       (select COUNT(*)
        from isBooked ib INNER JOIN
             booking b
             ON ib.BookingID = b.bookingID
        where c.calendarDate between b.startDate and b.endDate
       )
from calendar c
where c.calendardate between '2015-08-23' and DATE_ADD('2015-08-23', INTERVAL 6 DAY);