mysql - 如何获取一个月内剩余可预订天数？

Question

我有一个具有以下架构的插槽表

SlotId
FromDate
ToDate

我想在给定月份的剩余天数中进行时段预订。

明确地说，我正在尝试检索给定月份的所有日期，除了存储在数据库中的日期(因为这些日期已经预订)。

例如，如果一条记录的 FromDate 等于 2014-04-02 并且 ToDate 等于 2014- 04-06 我期待以下结果:

2014-04-01
2014-04-07
...
2014-04-30

虽然我使用 PHP 编写脚本，但我对实现此目的的查询不太感兴趣。

Answer 1

所以在 mysql 中做这件事不是一件容易的事，但这是应该可行的。这将获取任何给定月份中未预订的日期...请参阅 fiddle工作示例

SELECT *, union_month.day_date
FROM (
    SELECT 1 AS day_date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30 UNION ALL
    SELECT 31
) AS union_month
LEFT JOIN myTable AS t ON union_month.day_date <> DAY(t.to_date) OR union_month.day_date <> DAY(t.from_date)
WHERE union_month.day_date <= DAY(LAST_DAY(t.to_date))
AND union_month.day_date NOT BETWEEN DAY(t.from_date) AND DAY(t.to_date)
GROUP BY union_month.day_date

---

对于一个月中的多个日期，将 WHERE 子句更改为此

WHERE
      union_month.day_date <= DAY(LAST_DAY(t.to_date))
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 0,1) AND (select DAY(to_date) from myTable limit 0,1)
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 1,1) AND (select DAY(to_date) from myTable limit 1,1)
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 2,1) AND (select DAY(to_date) from myTable limit 2,1)
GROUP BY union_month.day_date

工作 fiddle多个日期