MySQL查询：基于Reset标记获取用户的最小有效时间

嘿，我来帮你搞定这个MySQL查询的问题！先理清楚你的场景和问题所在：

你的数据表结构 #

你有一个名为foo的数据表，数据如下：

你的需求 #

对每个userID：

• 如果该用户有context='Reset'的记录，需要找到大于等于该用户最晚Reset时间的最小time
• 如果用户没有Reset记录，直接返回该用户的最小time

原SQL的问题 #

你之前写的查询有几个关键错误：

1. 子查询(SELECT MIN(a.time) FROM foo)是取整个表的最小时间，不是当前用户的
2. (SELECT Max(a.time) FROM foo where context = 'Reset')是取所有用户的最晚Reset时间，不是当前用户的
3. 使用SELECT *配合GROUP BY userID不符合SQL规范（除非开启了MySQL的非严格模式），会导致返回的记录不确定

正确的查询方案 #

这里提供两种可行的解决方法，你可以根据自己的MySQL版本选择：

方法一：子查询关联（兼容所有MySQL版本）

SELECT 
    f.userID,
    MIN(f.time) AS time
FROM 
    foo f
LEFT JOIN (
    -- 先获取每个用户的最晚Reset时间
    SELECT 
        userID,
        MAX(time) AS reset_time
    FROM foo 
    WHERE context = 'Reset'
    GROUP BY userID
) r ON f.userID = r.userID
-- 筛选逻辑：要么没有Reset记录，要么当前记录的time >= 该用户的Reset时间
WHERE 
    r.reset_time IS NULL 
    OR f.time >= r.reset_time
GROUP BY 
    f.userID;

方法二：窗口函数（MySQL 8.0及以上版本可用）

如果你的MySQL是8.0或更高版本，用窗口函数会更简洁直观：

WITH user_reset_info AS (
    SELECT 
        userID,
        time,
        -- 给每个用户标记出他的最晚Reset时间（没有则为NULL）
        MAX(CASE WHEN context = 'Reset' THEN time END) OVER (PARTITION BY userID) AS reset_time
    FROM foo
)
SELECT 
    userID,
    MIN(time) AS time
FROM user_reset_info
WHERE 
    reset_time IS NULL 
    OR time >= reset_time
GROUP BY userID;

预期结果 #

这两个查询都会返回你想要的结果：

逻辑说明 #

• 对于有Reset记录的用户，我们先找到他的最晚Reset时间，再筛选出所有晚于等于这个时间的记录，取其中最小的time
• 对于没有Reset记录的用户，直接取他所有记录里的最小time

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