PostgreSQL中如何返回列值为前置行同列值超集的行

针对你的需求，我们需要筛选出同时满足以下两个条件的行：一是当前行的name是至少一个前置行（id更小的行）name的严格超集（示例中表现为前缀扩展）；二是当前行的name不会被任何后续行的name作为严格子集。以下是实现该逻辑的PostgreSQL SQL语句：

方法一：直接使用NOT EXISTS查询 #

SELECT t.id, t.name, t.datetime
FROM your_table t
WHERE 
    -- 存在前置行的name是当前name的严格前缀（当前name是前置行的超集）
    EXISTS (
        SELECT 1 
        FROM your_table t_prev
        WHERE t_prev.id < t.id
          AND t.name LIKE t_prev.name || '%'
          AND t.name != t_prev.name
    )
    -- 不存在后续行的name是当前name的严格前缀（当前name没有被后续行扩展）
    AND NOT EXISTS (
        SELECT 1 
        FROM your_table t_next
        WHERE t_next.id > t.id
          AND t_next.name LIKE t.name || '%'
          AND t_next.name != t.name
    )
ORDER BY t.id;

方法二：用CTE分步标记状态 #

如果需要更清晰的逻辑拆分，可以先用CTE标记每行的状态，再筛选目标行：

WITH row_status AS (
    SELECT 
        id, 
        name, 
        datetime,
        -- 标记是否存在前置子集行
        EXISTS (
            SELECT 1 
            FROM your_table t_prev
            WHERE t_prev.id < t.id
              AND t.name LIKE t_prev.name || '%'
              AND t.name != t_prev.name
        ) AS has_prev_subset,
        -- 标记是否存在后续超集行
        EXISTS (
            SELECT 1 
            FROM your_table t_next
            WHERE t_next.id > t.id
              AND t_next.name LIKE t.name || '%'
              AND t_next.name != t.name
        ) AS has_next_superset
    FROM your_table t
)
SELECT id, name, datetime
FROM row_status
WHERE has_prev_subset = TRUE
  AND has_next_superset = FALSE
ORDER BY id;

逻辑说明 #

1. 前置行判断：通过EXISTS子查询验证是否存在id更小的行，其name是当前行name的前缀且内容不同，确保当前行是前置行的超集。
2. 后续行判断：通过NOT EXISTS子查询确保没有id更大的行，其name是当前行name的前缀且内容不同，即当前行是该序列的最终扩展版本。

执行上述语句后，会返回你期望的结果：id4、9、11对应的行，它们都是各自序列中最后一个完成扩展的字符串，且都存在前置的子集行。

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