如何不使用LEAD、LAG或临时表标记指定行及其前后行

需求说明 #

为table1表添加flag列，规则如下：

• event='s'的行及其前一行、后一行的flag值为0
• 其余行flag值为1
• 限制：无法使用LEAD、LAG函数或临时表

表结构与测试数据 #

-- 创建表
CREATE TABLE table1 (
  id INTEGER PRIMARY KEY,
  time INTEGER,
  event varchar NOT NULL
);
-- 插入测试数据
INSERT INTO table1 VALUES (1, '1', 'r');
INSERT INTO table1 VALUES (2, '2', 'r');
INSERT INTO table1 VALUES (3, '3', 's');
INSERT INTO table1 VALUES (4, '4', 'r');
INSERT INTO table1 VALUES (5, '5', 'r');
INSERT INTO table1 VALUES (6, '6', 'r');
INSERT INTO table1 VALUES (7, '7', 's');
INSERT INTO table1 VALUES (8, '8', 'r');
INSERT INTO table1 VALUES (9, '9', 'r');
INSERT INTO table1 VALUES (10, '10', 's');

期望输出 #

+-----------+--------+------+
| timestamp | events | flag |
+-----------+--------+------+
|         1 | r      |    1 |
|         2 | r      |    0 |
|         3 | s      |    0 |
|         4 | r      |    0 |
|         5 | r      |    1 |
|         6 | r      |    1 |
|         7 | r      |    0 |
|         8 | s      |    0 |
|         9 | r      |    0 |
|        10 | r      |    0 |
|        11 | s      |    0 |
+-----------+--------+------+

当前尝试的问题 #

现有SQL仅能筛选出flag为0的行，缺少flag为1的行：

SELECT a.time, a.event, 0 as flag
FROM table1 AS a
JOIN table1 AS b
ON b.event = 's' AND abs(a.id - b.id) <= 1

完整解决方案 #

方法一：使用EXISTS子查询

SELECT 
    t.time AS timestamp,
    t.event AS events,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM table1 s 
            WHERE s.event = 's' AND ABS(t.id - s.id) <= 1
        ) THEN 0 
        ELSE 1 
    END AS flag
FROM table1 t
ORDER BY t.id;

方法二：使用LEFT JOIN + GROUP BY

SELECT 
    t.time AS timestamp,
    t.event AS events,
    IF(s.id IS NOT NULL, 0, 1) AS flag
FROM table1 t
LEFT JOIN table1 s 
    ON s.event = 's' AND ABS(t.id - s.id) <= 1
GROUP BY t.id, t.time, t.event
ORDER BY t.id;

说明 #

两种方法均通过关联event='s'的行，判断当前行id与目标行id的差值是否在±1范围内：

• 满足条件则flag设为0
• 不满足则设为1
• 方法二中的GROUP BY用于避免同一行匹配多个s行时产生重复记录

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