在PostgreSQL（DBeaver）中获取特定行及其前后行的技术需求

原始数据集 #

查询需求 #

获取所有identifier=1的行，同时获取这些行的直接上一行或直接下一行，结果按日期从最新到最旧排序。

规则说明：

• identifier=1的行必有下一行，可能存在上一行；
• 若某个ID不存在identifier=1的行，则该ID的所有行不纳入结果。

预期结果 #

解决方案（PostgreSQL SQL语句） #

方案一：基于行编号筛选 #

通过窗口函数为每个ID的行按入院日期倒序编号，再筛选目标行及其相邻行：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TO_DATE(Admission_Date, 'MM/DD/YY') DESC) AS rn,
        MAX(CASE WHEN Identifier = 1 THEN rn END) OVER (PARTITION BY ID) AS target_rn
    FROM your_table_name
)
SELECT 
    ID,
    Identifier,
    Admission_Date AS "Admission Date",
    Release_Date AS "Release Date"
FROM ranked_data
WHERE 
    (rn = target_rn OR rn = target_rn - 1 OR rn = target_rn + 1)
    AND target_rn IS NOT NULL
ORDER BY TO_DATE(Admission_Date, 'MM/DD/YY') DESC;

方案二：基于相邻行标记筛选 #

用LAG/LEAD窗口函数标记目标行的上下行，再过滤结果：

WITH flagged_data AS (
    SELECT 
        *,
        CASE 
            WHEN Identifier = 1 THEN 1
            WHEN LAG(Identifier) OVER (PARTITION BY ID ORDER BY TO_DATE(Admission_Date, 'MM/DD/YY') DESC) = 1 THEN 1
            WHEN LEAD(Identifier) OVER (PARTITION BY ID ORDER BY TO_DATE(Admission_Date, 'MM/DD/YY') DESC) = 1 THEN 1
            ELSE 0
        END AS is_target_related
    FROM your_table_name
),
valid_ids AS (
    SELECT DISTINCT ID FROM your_table_name WHERE Identifier = 1
)
SELECT 
    ID,
    Identifier,
    Admission_Date AS "Admission Date",
    Release_Date AS "Release Date"
FROM flagged_data
JOIN valid_ids USING (ID)
WHERE is_target_related = 1
ORDER BY TO_DATE(Admission_Date, 'MM/DD/YY') DESC;

关键注意事项 #

1. 替换your_table_name为实际表名；
2. 如果Admission_Date是字符串类型，必须用TO_DATE(Admission_Date, 'MM/DD/YY')转换为日期类型，避免字符串排序逻辑错误。

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