MySQL中objectDetails表按指定规则查询过滤数据的实现咨询

需求对应实现方案 #

方案1：MySQL 8.0及以上版本（窗口函数实现，性能更优） #

SELECT id, `Object`, user, status
FROM (
    SELECT 
        *,
        -- 按共享Object分组，统计该组内的最大status值
        MAX(status) OVER (PARTITION BY `Object`) AS obj_max_status
    FROM objectDetails
    -- 第一步过滤：只保留user为1、2、3的记录
    WHERE user IN (1, 2, 3)
) AS temp
-- 第二步过滤：要么该Object无status=3的记录全保留，要么只保留status=3的记录
WHERE obj_max_status < 3 OR status = 3;

方案2：MySQL 5.x兼容版本（子查询实现） #

SELECT o.*
FROM objectDetails o
WHERE o.user IN (1,2,3)
AND (
    -- 该共享Object在筛选后的结果中不存在status=3的记录，全部保留
    o.`Object` NOT IN (
        SELECT DISTINCT `Object`
        FROM objectDetails
        WHERE user IN (1,2,3) AND status = 3
    )
    -- 该条记录本身就是status=3的记录，直接保留
    OR o.status = 3
);

执行结果说明 #

以上两种SQL执行后，都会按照需求返回id为1、2、3的三条记录，完全符合筛选规则。

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