根据现有行选择月份的日期

Question

我有一个具有以下模式的表：

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bookingDate` date NOT NULL,
 PRIMARY KEY(`id`)

我需要获取当前月份中预订量少于5个的所有日期（即bookingDate == date的行数少于5行的日期）。

我尝试逐个遍历每个月的每一天，但出于性能原因，每个月执行+/- 30个查询真的没有意义。

- idk123123

---

你尝试过什么？具体哪里出了问题？ - undefined

提示：您可以使用GROUP BY和HAVING子句来计算每个日期的预订数量并过滤结果。 - undefined

GROUP BY一次性完成所有操作，而CTE是一种迭代的高级方式。 - undefined

@user1191247 - 这可能最好通过从一个包含所有日期的表格进行LEFT JOIN来完成。在https://mysql.rjweb.org/doc.php/index_cookbook_mysql#sequence中可以看到一个示例。 - undefined

@user1191247 - 类似的问题的缺点是没有使用MariaDB。此外，请注意MariaDB有两种类型的“排序”——一个是引擎，另一个是用于构建数字序列的伪表。后者可以很容易地被动态转换为一系列连续的日期，以满足OP的需求。 - undefined

Answer 1

为了更高效地实现这一目标，您可以使用一个查询。以下是解决方案的逐步分解：

1. 生成当前月份的所有日期。 2. 在预订表上使用LEFT JOIN将生成的日期与预订日期进行连接。 3. 按日期进行分组，并计算每个日期的预订数量。 4. 过滤掉有5个或更多预订的日期。

以下是您可以执行此操作的方法：

-- Step 1: Generate all dates for the current month
WITH RECURSIVE DateSequence AS (
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS dateValue -- Start from the first day of the current month
    UNION ALL
    SELECT DATE_ADD(dateValue, INTERVAL 1 DAY)
    FROM DateSequence
    WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= LAST_DAY(CURDATE()) -- Up to the last day of the current month
)

-- Step 2, 3, and 4
SELECT ds.dateValue
FROM DateSequence ds
LEFT JOIN YourTableName t ON ds.dateValue = t.bookingDate
GROUP BY ds.dateValue
HAVING COUNT(t.id) < 5;

将YourTableName替换为您的预订表的名称。此查询的结果将提供当前月份中预订少于5个的所有日期。

这种方法的优点是您可以通过一次查询获得结果，减少了多个单独查询的开销。

- Simon Price

Answer 2

有很多不同的方法来解决这个问题。

选项1（实际上不是一个选项）

SELECT bookingDate
FROM bookings
WHERE bookingDate BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY bookingDate
HAVING COUNT(*) < 5;

这将仅返回具有1、2、3或4个预订的bookingDate，因为它不知道没有预订的日期。 选项2 您可以反转上述查询，并在客户端应用程序中迭代结果，取消所有存在于结果中的日期。

SELECT bookingDate
FROM bookings
GROUP BY bookingDate
HAVING COUNT(*) >= 5;

但是，对我来说，那只是感觉笨拙。选项3，你可以使用MariaDB的Sequence Storage Engine生成你感兴趣的全部日期列表开始。

/* Full list of days in September 2023 based on first
 * of month and number of days in month */
SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_30;

/* Full list of days in current month */
SELECT CURRENT_DATE - INTERVAL (DAY(CURRENT_DATE) - 1) DAY + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_31
WHERE seq <= DAY(LAST_DAY(CURRENT_DATE));

显然，如果您已经有一个包含所有日期的日历表或连续的日期集合，那么就不需要进行这一步骤。

然后，将其与您的预订表进行左连接。

SELECT s.dt, COUNT(b.id) AS num_bookings
FROM (
    SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
    FROM seq_1_to_30
) s
LEFT JOIN bookings b
    ON s.dt = b.bookingDate
GROUP BY s.dt
HAVING num_bookings < 5;

这是一个可以玩耍的db<>fiddle链接。

---

注意：您当前的表缺少对bookingDate字段的索引。

ALTER TABLE bookings ADD INDEX idx_booking_date (bookingDate);

- user1191247

Answer 3

希望这段代码对你有所帮助：

-- 获取当前月份中预订量少于5个的日期

SELECT DISTINCT bookingDate
FROM bookings # your-table-name
WHERE MONTH(bookingDate) = MONTH(CURRENT_DATE()) AND YEAR(bookingDate) = YEAR(CURRENT_DATE())
GROUP BY bookingDate
HAVING COUNT(*) < 5;

- Amin Zayeromali

Answer 4

相反，你可以找到所有有5个或更多预订的日期，并在你的客户端应用程序上实施相应的逻辑。

SELECT bookingDate
FROM bookings
GROUP BY bookingDate
HAVING COUNT(bookingDate) > 4;

- Jarmo T

---

这不就是我回答中的“选项2”吗？ - undefined

为什么条件中超过4个？ - undefined