Oracle SQL按条件筛选最大ID：匹配ZQ123且优先含前置00记录

需求概述 #

基于Team分组筛选最大ID的记录，规则如下：

• 仅考虑Rooms列包含ZQ123的记录
• 优先选择Rooms中同时存在带前置00房间号的记录里的最大ID
• 若这类记录不存在，则选择仅Rooms包含ZQ123的记录中的最大ID

样本数据 #

解决方案（SQL） #

以MySQL为例，通过窗口函数实现优先级排序后筛选目标记录：

WITH ranked_records AS (
    SELECT 
        *,
        -- 标记优先级：含00前缀的记录优先级更高（值越小越优先）
        CASE 
            WHEN Rooms LIKE '%00%' THEN 1 
            ELSE 2 
        END AS priority,
        -- 按team分组，先按优先级升序、再按ID降序排名
        ROW_NUMBER() OVER (
            PARTITION BY team 
            ORDER BY 
                CASE WHEN Rooms LIKE '%00%' THEN 1 ELSE 2 END, 
                ID DESC
        ) AS rn
    FROM your_table_name
    WHERE Rooms LIKE '%ZQ123%' -- 先过滤包含ZQ123的记录
)
SELECT ID, name, team, Rooms
FROM ranked_records
WHERE rn = 1; -- 取每组排名第一的记录

逻辑解释 #

1. 基础筛选：用WHERE Rooms LIKE '%ZQ123%'过滤出所有包含ZQ123的记录
2. 优先级标记：通过CASE语句给存在00前缀房间的记录标记更高优先级
3. 分组排名：使用ROW_NUMBER()窗口函数按Team分组，先按优先级排序，再按ID降序排序，确保每组优先级最高的最大ID记录排第一
4. 提取结果：筛选出排名为1的记录，即为每组符合要求的目标记录

验证结果 #

执行上述SQL后，将得到期望结果：

内容的提问来源于stack exchange，提问作者SQL-newbee