如何在PostgreSQL中筛选特定记录：按规则获取X/Y/Z标记数据

表结构 #

Table A { int keyA, Text name}
Table B { int keyB, int keyA, char mark, date start, date end}

Table B的mark字段取值为'X'、'Y'、'Z'。

需求说明 #

• 获取所有标记为'X'的记录；
• 若某个keyA对应的记录中不存在'X'，则仅获取一条'Y'或'Z'的记录；
• 当'X'、'Y'、'Z'共存时，仅保留'X'记录。

源数据 #

期望结果 #

已尝试的查询方式 #

1. 子查询方式 #

Select A.name, 
(select b2.start from B b2 where b2.keyA = A.keyA and b2.mark = 'X') as Start,
(select b2.end from B b2 where b2.keyA = A.keyA and b2.mark = 'X') as End,
from A order by name;

问题：子查询返回多条记录时会报错，加limit 1只能取一条'X'，不符合获取所有'X'记录的要求；同时需要将name字段放在结果首位。

2. 内连接方式 #

Select A.name, B.start, B.end
from A inner join B on A.keyA = B.keyB

问题：会返回所有'X'、'Y'、'Z'记录，不符合筛选需求。

解决方案 #

使用窗口函数ROW_NUMBER()结合优先级判断实现，具体SQL语句如下：

SELECT 
    A.name,
    B_filtered.keyB,
    B_filtered.keyA,
    B_filtered.mark,
    B_filtered.start,
    B_filtered.end
FROM 
    A
JOIN (
    SELECT 
        *,
        -- 标记优先级：X为最高优先级1，其他为2
        CASE WHEN mark = 'X' THEN 1 ELSE 2 END AS priority,
        -- 按分组内优先级排序，同优先级按keyB排序（确保取固定的一条非X记录）
        ROW_NUMBER() OVER (PARTITION BY keyA ORDER BY CASE WHEN mark = 'X' THEN 1 ELSE 2 END, keyB) AS rn,
        -- 统计分组内是否存在X记录
        MAX(CASE WHEN mark = 'X' THEN 1 ELSE 0 END) OVER (PARTITION BY keyA) AS has_x
    FROM 
        B
) AS B_filtered ON A.keyA = B_filtered.keyA
WHERE 
    -- 有X则保留所有X，无X则保留第一条非X记录
    (B_filtered.has_x = 1 AND B_filtered.priority = 1) OR (B_filtered.has_x = 0 AND B_filtered.rn = 1)
ORDER BY 
    A.name, B_filtered.keyB;

语句说明 #

• 子查询B_filtered中：
  • priority字段标记记录优先级，'X'为1，'Y'/'Z'为2；
  • has_x字段统计每个keyA分组内是否存在'X'；
  • rn字段为分组内记录按优先级排序后的序号；
• 外层WHERE条件：
  • 分组存在'X'时，仅保留所有优先级为1的记录；
  • 分组无'X'时，仅保留序号为1的非'X'记录；
• 最终按name和keyB排序，保证结果有序。

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