mysql - 如何返回按实体分组的最近时间段，但仅返回最近的可用日期，而不是每个可用日期？

Question

我有一张表，用于存储教师的可用约会，以小时为单位，可以完全自由地为每位教师每天添加无限个空档(只要空档不重叠)。示例简化结构:

CREATE TABLE time_slots (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  teacher_id mediumint(8) unsigned NOT NULL,
  slot bigint(20) unsigned NOT NULL DEFAULT '0',
 );

slot 列存储插槽的时间戳。

如何为每位教师显示最接近可用日期的所有可用空档？注意:显示给定日期的所有时间段，同一位老师不能显示超过一天。

示例数据:

PS:使用 datetime 只是为了让事情更具可读性。

+----+------------+------------------+
| id | teacher_id | slot             |
+----+------------+------------------+
| 1  | 1          | 2013-04-10 08:00 |
| 2  | 1          | 2013-04-10 09:00 |
| 3  | 1          | 2013-04-10 09:30 |
| 4  | 1          | 2013-04-11 08:00 |
| 5  | 1          | 2013-04-11 09:00 |
| 6  | 1          | 2013-04-11 10:30 |
| 7  | 2          | 2013-04-12 07:00 |
| 8  | 2          | 2013-04-12 09:00 |
| 9  | 2          | 2013-04-14 08:00 |
+----+------------+------------------+

预期结果:

假设搜索时间:2013-04-10 08:30，返回的结果必须是:

+----+------------+------------------+
| id | teacher_id | slot             |
+----+------------+------------------+
| 2  | 1          | 2013-04-10 09:00 |
| 3  | 1          | 2013-04-10 09:30 |
| 7  | 2          | 2013-04-12 07:00 |
| 8  | 2          | 2013-04-12 09:00 |
+----+------------+------------------+

1. 不要显示 id 1，因为它已经从 4 月 10 日开始的 08:00 过去了。
2. 不要显示 id 4、5、6，因为我们发现距离 teacher = 1 最近的可用位置是 id 的 2 和 3。
3. 不要显示 id 9，因为对于 teacher = 2，最接近的可用空位是 7 和 8，所以不要在另一天返回。

我尝试了什么

我很难提出查询，我只是提出了这个基本查询，但它没有假设只获得第一个可用的日期，当然它不会返回给定日期的所有可用时段.它只为每位教师返回 1 个插槽:

SELECT id, teacher_id, FROM_UNIXTIME(slot)
FROM time_slots
WHERE slot >= [Actual timestamp]
GROUP BY DATE(FROM_UNIXTIME(slot))
ORDER BY slot, teacher_id ASC

注意:我这里使用FROM_UNIXTIME只是为了调试目的，当然我会在后面优化索引等。

Answer 1

首先，您需要查询以获取每位教师最接近的天，因为每位教师的日期都不一样。一些伪 SQL 在这里这样做:

SELECT
  MIN(DATE(slot)) as closestDay,
  teacher_id
FROM time_slots
WHERE slot >= NOW()
GROUP BY teacher_id

然后执行该查询并使用结果显示每天的所有可用时段

SELECT 
   id,
   time_slots.teacher_id,
   FROM_UNIXTIMESTAMP(slot)
FROM time_slots
JOIN (
    SELECT
      MIN(DATE(slot)) as closestDay,
      teacher_id
    FROM time_slots
    WHERE slot >= NOW()
    GROUP BY teacher_id
) a
ON a.teacher_id = time_slots.teacher_id
  AND DATE(time_slots.slot) = closestDay
WHERE time_slots.slot >= NOW()

它显然没有经过测试，但它应该能为您提供大致的思路。