MySQL多条件查询：带规则的feedback表最新数据筛选需求

表结构与示例数据 #

需求说明 #

在指定时间范围（示例：2022-11-18 15:00:00 至 2022-11-18 15:30:00）内，按user_id分组获取最新的feedback_val记录，规则如下：

• 若某user_id存在指定ext_id（示例：adef1234）的记录，则忽略该用户所有ext_id为NULL的记录，仅从指定ext_id的记录中取最新值
• 若用户无指定ext_id的记录，则取该用户时间范围内的最新记录

解决方案（MySQL 8.0+ 推荐） #

使用窗口函数ROW_NUMBER()可以简洁实现需求，逻辑清晰且性能较好：

WITH filtered_feedback AS (
    SELECT 
        user_id,
        feedback_val,
        origin_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY 
                -- 优先保留指定ext_id的记录，同类型按时间倒序
                CASE WHEN ext_id = 'adef1234' THEN 1 ELSE 2 END,
                origin_timestamp DESC
        ) AS rn
    FROM feedback
    WHERE origin_timestamp BETWEEN '2022-11-18 15:00:00' AND '2022-11-18 15:30:00'
    -- 核心过滤：用户有指定ext_id记录则只保留这些，否则保留所有
    AND (
        ext_id = 'adef1234'
        OR NOT EXISTS (
            SELECT 1 
            FROM feedback f2
            WHERE f2.user_id = feedback.user_id
              AND f2.ext_id = 'adef1234'
              AND f2.origin_timestamp BETWEEN '2022-11-18 15:00:00' AND '2022-11-18 15:30:00'
        )
    )
)
SELECT user_id, feedback_val, origin_timestamp
FROM filtered_feedback
WHERE rn = 1;

逻辑解释 #

1. CTE过滤阶段：先筛选出符合时间范围的记录，并根据规则过滤掉不需要的记录（有指定ext_id的用户只保留该类记录）
2. 窗口函数排序：按user_id分组，先把指定ext_id的记录排在前面，同类型内按时间倒序，这样最新的记录会被标记为rn=1
3. 取结果：筛选出每个分组中rn=1的记录，即为目标结果

兼容MySQL 5.x版本的方案 #

如果使用不支持窗口函数的旧版本MySQL，可以用子查询实现：

SELECT 
    f1.user_id,
    f1.feedback_val,
    f1.origin_timestamp
FROM feedback f1
WHERE 
    f1.origin_timestamp BETWEEN '2022-11-18 15:00:00' AND '2022-11-18 15:30:00'
    AND (
        f1.ext_id = 'adef1234'
        OR NOT EXISTS (
            SELECT 1 
            FROM feedback f2
            WHERE f2.user_id = f1.user_id
              AND f2.ext_id = 'adef1234'
              AND f2.origin_timestamp BETWEEN '2022-11-18 15:00:00' AND '2022-11-18 15:30:00'
        )
    )
    AND NOT EXISTS (
        SELECT 1 
        FROM feedback f3
        WHERE 
            f3.user_id = f1.user_id
            AND (
                f3.ext_id = 'adef1234'
                OR NOT EXISTS (
                    SELECT 1 
                    FROM feedback f4
                    WHERE f4.user_id = f3.user_id
                      AND f4.ext_id = 'adef1234'
                      AND f4.origin_timestamp BETWEEN '2022-11-18 15:00:00' AND '2022-11-18 15:30:00'
                )
            )
            AND (
                -- 确保当前记录是同类型中最新的
                (f3.ext_id = 'adef1234' AND f1.ext_id = 'adef1234' AND f3.origin_timestamp > f1.origin_timestamp)
                OR (f3.ext_id IS NULL AND f1.ext_id IS NULL AND f3.origin_timestamp > f1.origin_timestamp)
            )
    )
ORDER BY f1.user_id;

执行结果 #

两种方案都会返回符合需求的结果：

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