MariaDB 10.4+ SQL查询：存在指定id_r时排除对应id_r=0记录

问题描述 #

假设有一张表（这里姑且叫menu_records，原问题里写的database是关键字，请勿使用），字段包含ir_f、id_r、description，数据如下：

现在需要查询ir_f=2的记录，规则为：如果某条通用记录（id_r=0）存在对应的餐厅专属记录（id_r>0，比如示例中linea 16对应linea 16 modified），则仅保留专属记录、删除对应通用记录；其余无专属记录的通用记录和所有专属记录均保留。最终目标结果如下：

补充规则：id_r=0代表适用于所有餐厅的通用记录，id_r>0代表仅适用于对应餐厅的专属记录，专属记录优先级更高，需替换掉对应的通用记录。

解决方案 #

方法一：用NOT EXISTS过滤被替换的通用记录 #

写法直接明了，先筛选所有专属记录，再筛选未被替换的通用记录：

SELECT ir_f, id_r, description
FROM menu_records
WHERE ir_f = 2
AND (
    -- 保留所有餐厅专属记录
    id_r > 0
    OR (
        -- 保留通用记录，但排除存在对应专属记录的条目
        id_r = 0
        AND NOT EXISTS (
            SELECT 1
            FROM menu_records t2
            WHERE t2.ir_f = menu_records.ir_f
            AND t2.id_r > 0
            -- 匹配逻辑：专属记录描述以通用记录描述开头
            AND t2.description LIKE CONCAT(menu_records.description, '%')
        )
    )
)
ORDER BY description;

方法二：用窗口函数标记优先级（适合扩展场景） #

如果后续需要处理多餐厅、更复杂的优先级逻辑，用窗口函数分组排序会更灵活：

WITH ranked_data AS (
    SELECT 
        ir_f, 
        id_r, 
        description,
        -- 将通用记录与对应专属记录归为一组：提取描述核心部分（如linea 16）
        CASE 
            WHEN id_r > 0 THEN SUBSTRING_INDEX(description, ' ', 2)
            ELSE description
        END AS core_desc,
        -- 给每组记录排序：专属记录优先级为1（排前），通用记录优先级为2（排后）
        ROW_NUMBER() OVER (
            PARTITION BY ir_f, core_desc
            ORDER BY CASE WHEN id_r > 0 THEN 1 ELSE 2 END
        ) AS rank_num
    FROM menu_records
    WHERE ir_f = 2
)
-- 仅保留每组中优先级最高的第一条记录
SELECT ir_f, id_r, description
FROM ranked_data
WHERE rank_num = 1
ORDER BY description;

注意事项 #

• 请勿使用database作为表名，这是MariaDB关键字，会导致语法错误，替换为实际业务表名（如menu_records）
• 如果description格式发生变化（如不再是linea X modified结构），需调整匹配逻辑，确保能准确关联通用记录与对应专属记录

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