如何用SQL获取fruits表中high_low_adjstmnt<0的行及其前后行？

样本表结构与数据 #

现有fruits表的结构及数据如下：

id  name    low high    high_low_adjstmnt      volume
1   Apple   5   3         -2                   1000
2   Orange  6   9          3                   2000
3   Banana  13  17         4                   3000
4   Avocado 11  19         8                   4000
5   Berry   21  17        -4                   5000
6   Peach   7   9          2                   6000
7   Mango   9   14         5                   7000
8   Grape   18  11         7                   8000
9   Kiwi    14  13        -1                   9000

查询需求 #

需要查询出所有high_low_adjstmnt < 0的行，同时包含这些行对应的前一行和后一行，期望输出结果如下：

id  name    low high    high_low_adjstmnt      volume
1   Apple   5   3         -2                   1000
2   Orange  6   9          3                   2000
4   Avocado 11  19         8                   4000
5   Berry   21  17        -4                   5000
6   Peach   7   9          2                   6000
8   Grape   18  11         7                   8000
9   Kiwi    14  13        -1                   9000

SQL实现方案 #

方案一：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

利用窗口函数LAG()和LEAD()标记目标行及其前后行，再筛选符合条件的记录：

WITH flagged_rows AS (
    SELECT 
        *,
        -- 标记当前行是否为目标行
        CASE WHEN high_low_adjstmnt < 0 THEN 1 ELSE 0 END AS is_target,
        -- 标记前一行是否为目标行
        LAG(CASE WHEN high_low_adjstmnt < 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS prev_is_target,
        -- 标记后一行是否为目标行
        LEAD(CASE WHEN high_low_adjstmnt < 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS next_is_target
    FROM fruits
)
SELECT 
    id, name, low, high, high_low_adjstmnt, volume
FROM flagged_rows
WHERE 
    is_target = 1 
    OR prev_is_target = 1 
    OR next_is_target = 1
ORDER BY id;

逻辑说明

1. 通过CTE（公共表表达式）为每一行添加三个标记字段，分别标识当前行、前一行、后一行是否为目标行；
2. 筛选出满足「当前行是目标行/前一行是目标行/后一行是目标行」任一条件的记录；
3. 按id排序输出，与期望结果一致。

方案二：兼容低版本数据库（如MySQL 5.x） #

如果数据库不支持窗口函数，可通过自连接实现：

SELECT DISTINCT
    f.id, f.name, f.low, f.high, f.high_low_adjstmnt, f.volume
FROM fruits f
LEFT JOIN fruits target ON 
    f.id = target.id 
    OR f.id = target.id + 1 
    OR f.id = target.id - 1
WHERE target.high_low_adjstmnt < 0
ORDER BY f.id;

逻辑说明

通过自连接将目标行及其前后行关联，用DISTINCT去重后筛选排序，最终得到需求结果。

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