基于成员最新状态查询MySQL指定日期的俱乐部在册成员

• 两个嵌套子查询未关联当前行的Club、Student维度，相当于全表全局取最大的Joined日期、最大的Left日期做对比，完全无法按学生维度计算真实加入/退出时间差，甚至语法层面都缺少表关联、条件绑定逻辑无法正常执行
• 没有加入任意时间点的过滤条件，仅能计算当前全表的最终状态，无法回溯历史节点的成员状态
• 边界逻辑缺失：没有考虑同一学生在查询当天发生加入/退出操作的状态归属，和示例需求的时间匹配逻辑不兼容

核心判定逻辑：对指定俱乐部、指定查询时间点，取每个学生在该时间点**之前（含当天）**的最后一条操作记录，如果最后一条记录状态为Joined，即代表该学生在目标时间点处于在册状态。

高版本MySQL（8.0+）实现（窗口函数方案） #

以查询2020-01-09 ABC俱乐部在册成员为例，SQL如下：

WITH filtered_membership AS (
    SELECT
        Club,
        Student,
        Status,
        -- 按学生+俱乐部维度，把时间点之前的操作按倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY Club, Student
            ORDER BY Date DESC
        ) AS op_rank
    FROM membership
    WHERE
        Club = 'ABC'
        -- 替换为任意需要查询的时间点即可
        AND Date <= '2020-01-09'
)
SELECT Student
FROM filtered_membership
WHERE
    op_rank = 1
    AND Status = 'Joined';

• 若将查询时间点替换为2020-01-12，返回结果为Alice、Bob，和示例预期完全匹配：当天Alice的最后操作是Joined，Charlie的最后操作是Left，Bob的最后操作是Joined。

低版本MySQL实现（关联子查询方案） #

针对不支持CTE、窗口函数的低版本环境，可通过NOT EXISTS实现相同判定逻辑：

SELECT DISTINCT m1.Student
FROM membership m1
WHERE
    m1.Club = 'ABC'
    -- 替换为任意需要查询的时间点即可
    AND m1.Date <= '2020-01-09'
    -- 判定：当前记录是该学生在目标俱乐部、目标时间点之前的最晚操作
    AND NOT EXISTS (
        SELECT 1
        FROM membership m2
        WHERE
            m2.Club = m1.Club
            AND m2.Student = m1.Student
            AND m2.Date > m1.Date
            AND m2.Date <= '2020-01-09'
    )
    AND m1.Status = 'Joined';

• 日期字段建议采用标准YYYY-MM-DD格式存储，避免字符串排序、格式解析导致的时间边界错误
• 逻辑中查询时间点可直接作为参数传入，不需要调整核心查询结构，即可快速回溯任意历史时间点的成员状态
• 需保证同一操作（加入/退出）的时间精度一致，避免出现时间重叠导致的状态判定偏差

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