MySQL 在接下来的 7 天内每天计数 (*) [英] MySQL count(*) each day for next 7 days

问题描述

我有以下查询来计算选定日期的预订数量

I have the following query which counts the number of bookings for a selected day

 select count(*) 
 from  isBooked inner join booking
 on isbooked.BookingID = booking.bookingID
 where '2015-08-09' between booking.startDate and booking.endDate;

我想在接下来的 7 天内运行此查询并显示每天的计数，例如

I want to run this query for the next 7 days and display the count for each day, for example

day     count
1        10
2        9
3        18
4        6
5        1
6        9
7        14

推荐答案

基本上可以是 7 个查询的 UNION :

Basically it can be an UNION of 7 queries :

(
    SELECT CURDATE() AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE CURDATE() BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 1 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 2 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 3 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 4 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 5 DAY) BETWEEN booking.startDate and booking.endDate;
)
UNION ALL
(
    SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY) AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE DATE_ADD(CURDATE(), INTERVAL 6 DAY) BETWEEN booking.startDate and booking.endDate;
)

但是如果您有大量数据需要管理，您应该考虑在您的数据库上定期处理这些数据并将其放入某个缓存文件或其他表中.

But if you have a large amount of data to manage, you shoud consider processing it regularily on your database and putting it in some cache file or other table.

如果您想长时间执行此操作，N 个 SELECT 的 UNION 将没有效率.在这种情况下，我会建议定义一个包含日期的(临时)表，并在日期上使用 JOIN 进行单一查询，例如:

If you want to do this for a lot of days, an UNION of N SELECT's will not be efficient. In this case I would recommand defining a (temporary) table containing the days, and doing a sigle query with a JOIN on the dates, for example :

CREATE TEMPORARY TABLE dates (day DATE); -- not necessarily temporary

INSERT INTO dates (day) values ('2015-01-01'), ....

SELECT dates.day AS Date, COUNT(*) AS Available
        FROM isBooked INNER JOIN booking
            ON isbooked.BookingID = booking.bookingID
        WHERE dates.day BETWEEN booking.startDate and booking.endDate
        GROUP BY dates.day;

这篇关于MySQL 在接下来的 7 天内每天计数 (*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！