MySQL 在接下来的 7 天内每天计数 (*)

本文介绍了MySQL 在接下来的 7 天内每天计数 (*)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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;