基于evaluation_chart日期筛选指定用户appointments的SQL查询需求

我们可以通过**窗口函数ROW_NUMBER()**结合优先级排序实现需求，确保每个指定用户仅返回一条符合条件的预约记录。以下是具体SQL实现：

WITH ranked_appointments AS (
    SELECT
        a.*,
        -- 标记优先级：1最高，3最低
        CASE
            WHEN ec.appointment_id IS NOT NULL THEN 1
            WHEN a.appt_time >= CURRENT_DATE THEN 2
            ELSE 3
        END AS priority,
        -- 按优先级对应的规则排序，生成每组内的记录编号
        ROW_NUMBER() OVER (
            PARTITION BY a.user_id
            ORDER BY
                CASE
                    WHEN ec.appointment_id IS NOT NULL THEN 1
                    WHEN a.appt_time >= CURRENT_DATE THEN 2
                    ELSE 3
                END ASC,
                -- 优先级1：取evaluation_chart最新修改的关联预约
                CASE WHEN ec.appointment_id IS NOT NULL THEN ec.updated_at END DESC,
                -- 优先级2：取最早的今日/未来预约
                CASE WHEN a.appt_time >= CURRENT_DATE THEN a.appt_time END ASC,
                -- 优先级3：取最近的历史预约
                CASE WHEN a.appt_time < CURRENT_DATE THEN a.appt_time END DESC
        ) AS rn
    FROM appointments a
    LEFT JOIN evaluation_chart ec ON a.appointment_id = ec.appointment_id
    WHERE a.user_id IN (4, 7) -- 指定目标用户ID
)
SELECT *
FROM ranked_appointments
WHERE rn = 1;

关键逻辑说明 #

• 优先级划分：通过CASE语句给每条预约标记优先级，关联了evaluation_chart的记录优先级最高，其次是今日/未来的无关联预约，最后是历史预约。
• 排序规则：
  • 关联evaluation_chart的记录，按该表的更新时间降序，确保取到最新修改或创建的关联记录。
  • 今日/未来的无关联预约，按预约时间升序，取最早的符合条件的预约（若需取最近的可改为降序，按需调整）。
  • 历史预约按时间降序，取最近的一条。
• 窗口函数分组取数：ROW_NUMBER()按用户ID分组，给每组内的记录按优先级+排序规则编号，最终筛选出编号为1的记录，即每个用户最符合要求的一条预约。

注意事项 #

• 请根据实际表结构调整字段名（如appt_time、updated_at等）。
• 若需用evaluation_chart的创建时间判断最新记录，将ec.updated_at替换为ec.created_at即可。
• 如果需要精确到当前时间判断“今日/未来”，将CURRENT_DATE改为CURRENT_TIMESTAMP。

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