日程调度应用技术咨询：当日/近期日程查询与数据库设计

先明确数据库结构 #

先把你给出的表结构整理成更清晰的格式：

-- members表
member_id

-- schedule表
schedule_id
schedule_name

-- assigned_schedules（asch表）
member_id
schedule_id
effective_from  -- Unix时间戳，日程生效开始时间
effective_to    -- Unix时间戳，日程生效结束时间

核心思路 #

我们需要针对每个成员，优先匹配当前时间处于effective_from和effective_to之间的日程（当日有效）；如果没有这样的日程，就匹配effective_from最接近当前时间且大于当前时间的下一个日程。

这里假设当前时间的Unix时间戳可以用数据库内置函数获取（比如MySQL的UNIX_TIMESTAMP()，PostgreSQL的extract(epoch from now())，根据你用的数据库调整）。

解决方案SQL（以MySQL为例） #

WITH ranked_schedules AS (
    SELECT
        a.member_id,
        s.schedule_name,
        a.effective_from,
        a.effective_to,
        -- 给每个成员的日程排序：当日有效的排第一，然后按即将到来的时间先后排序
        ROW_NUMBER() OVER (
            PARTITION BY a.member_id
            ORDER BY
                -- 先判断是否是当前生效的日程：是则优先级1，否则2
                CASE
                    WHEN UNIX_TIMESTAMP() BETWEEN a.effective_from AND a.effective_to THEN 1
                    ELSE 2
                END,
                -- 对于非当前生效的，取effective_from最小的（最近的下一个）
                a.effective_from ASC
        ) AS rn
    FROM assigned_schedules a
    JOIN schedule s ON a.schedule_id = s.schedule_id
    -- 过滤掉已经过期的日程（effective_to < 当前时间的，不需要考虑）
    WHERE a.effective_to >= UNIX_TIMESTAMP() OR a.effective_to IS NULL
)
SELECT
    member_id,
    schedule_name,
    FROM_UNIXTIME(effective_from) AS effective_from_datetime,
    FROM_UNIXTIME(effective_to) AS effective_to_datetime
FROM ranked_schedules
WHERE rn = 1;

代码解释 #

• CTE部分（ranked_schedules）：
  • 关联assigned_schedules和schedule表，拿到日程的名称；
  • 用ROW_NUMBER()窗口函数按成员分组排序：
    • 首先把当前生效的日程（当前时间在effective_from和effective_to之间）排在最前面；
    • 对于非当前生效的，按effective_from升序排序，这样最接近当前时间的下一个日程会排在前面；
  • 过滤条件排除已经完全过期的日程（effective_to < 当前时间），如果effective_to为NULL表示永久生效，也会保留。
• 最终查询：
  • 只取每个成员排序后的第一条记录（rn=1），也就是我们需要的当日或下一个即将到来的日程；
  • 用FROM_UNIXTIME()把Unix时间戳转成可读性更好的日期时间格式（可选，根据需求调整）。

注意事项 #

• 如果你的数据库不是MySQL，需要调整时间相关函数：比如PostgreSQL用extract(epoch from now())代替UNIX_TIMESTAMP()，用to_timestamp(effective_from)代替FROM_UNIXTIME()；
• 如果存在多个同时生效的当日日程，这个查询会只返回其中一条（按窗口排序的规则），如果需要返回所有当日生效的，可以把ROW_NUMBER()换成RANK()，并调整最终的过滤条件；
• 如果某个成员没有任何有效日程（所有日程都过期了），这个查询不会返回该成员的记录，如果你需要返回这类成员并标记“无有效日程”，可以左关联members表并处理NULL值。

内容的提问来源于stack exchange，提问作者Ice76