如何用SQL查询手术前24小时内的患者最近血压记录

核心思路 #

1. 先筛选出血压记录时间在手术时间前24小时内的有效数据
2. 按患者分组，将每组内的有效血压记录按时间倒序排序
3. 提取每组排序后的第一条记录，即为该患者符合要求的最近一次血压数据

通用SQL实现（支持窗口函数的数据库：MySQL 8+/PostgreSQL/SQL Server等） #

WITH filtered_bp_records AS (
    SELECT 
        `Patient ID`,
        Surgerytime,
        `Blood Pressure time`,
        Value,
        -- 按患者分组，血压时间倒序标记行号
        ROW_NUMBER() OVER (
            PARTITION BY `Patient ID` 
            ORDER BY `Blood Pressure time` DESC
        ) AS record_rank
    FROM your_table_name
    WHERE 
        -- 筛选手术前24小时内的血压记录
        `Blood Pressure time` >= DATEADD(HOUR, -24, Surgerytime)
        AND `Blood Pressure time` < Surgerytime
)
-- 提取每组的第一条记录
SELECT 
    `Patient ID`,
    Surgerytime,
    `Blood Pressure time`,
    Value
FROM filtered_bp_records
WHERE record_rank = 1;

不同数据库的适配调整 #

MySQL 版本

MySQL中日期加减使用DATE_ADD函数：

WITH filtered_bp_records AS (
    SELECT 
        `Patient ID`,
        Surgerytime,
        `Blood Pressure time`,
        Value,
        ROW_NUMBER() OVER (
            PARTITION BY `Patient ID` 
            ORDER BY `Blood Pressure time` DESC
        ) AS record_rank
    FROM your_table_name
    WHERE 
        `Blood Pressure time` >= DATE_ADD(Surgerytime, INTERVAL -24 HOUR)
        AND `Blood Pressure time` < Surgerytime
)
SELECT 
    `Patient ID`,
    Surgerytime,
    `Blood Pressure time`,
    Value
FROM filtered_bp_records
WHERE record_rank = 1;

PostgreSQL 版本

PostgreSQL直接用时间加减语法：

WITH filtered_bp_records AS (
    SELECT 
        "Patient ID",
        Surgerytime,
        "Blood Pressure time",
        Value,
        ROW_NUMBER() OVER (
            PARTITION BY "Patient ID" 
            ORDER BY "Blood Pressure time" DESC
        ) AS record_rank
    FROM your_table_name
    WHERE 
        "Blood Pressure time" >= Surgerytime - INTERVAL '24 hours'
        AND "Blood Pressure time" < Surgerytime
)
SELECT 
    "Patient ID",
    Surgerytime,
    "Blood Pressure time",
    Value
FROM filtered_bp_records
WHERE record_rank = 1;

注意事项 #

• 将代码中的your_table_name替换为你的实际表名
• 如果存在多条血压记录时间完全相同的情况，ROW_NUMBER()会随机选取一条；若需保留所有同时间的记录，可改用RANK()并筛选record_rank <= 1
• 确保Surgerytime和Blood Pressure time字段为datetime/timestamp类型，避免日期比较错误

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