基于日期范围匹配：检查前4行拼接列重复值并修正SQL

现有表结构与数据 #

表t1的结构和初始数据如下：

需求说明 #

拼接e、f两列生成字符串，检查当前行的拼接值是否存在于当前行之前的4行中：

• 若存在，将result设为1
• 若不存在（或当前行不足4个前置行），将result设为0

原错误SQL分析 #

原SQL逻辑错误，它总是查询表中最早的4行，未关联当前行的日期范围，无法实现"检查当前行之前4行"的需求：

UPDATE t1 
SET sesult = (CASE        
       WHEN 
            CONCAT(e,f) in(select CONCAT(e,f) from t1 order by date ASC LIMIT 4)   
      THEN 0 ELSE 1 END)

正确SQL实现 #

方案1：基于窗口函数的高效实现（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

通过窗口函数给每行按日期排序分配行号，精准定位当前行的前4行：

WITH ranked_rows AS (
    SELECT 
        *,
        CONCAT(e, f) AS ef_concat,
        ROW_NUMBER() OVER (ORDER BY date) AS row_num
    FROM t1
)
UPDATE t1
JOIN ranked_rows rr1 ON t1.date = rr1.date
SET result = CASE
    WHEN EXISTS (
        SELECT 1
        FROM ranked_rows rr2
        WHERE rr2.row_num BETWEEN rr1.row_num - 4 AND rr1.row_num - 1
        AND rr2.ef_concat = rr1.ef_concat
    ) THEN 1
    ELSE 0
END;

方案2：兼容老版本数据库的关联查询（适用于MySQL 5.x等） #

通过子查询筛选当前行的前4行并检查匹配：

UPDATE t1
SET result = CASE
    WHEN EXISTS (
        SELECT 1
        FROM t1 t2
        WHERE t2.date < t1.date
        AND (
            SELECT COUNT(*) FROM t1 t3 WHERE t3.date > t2.date AND t3.date <= t1.date
        ) < 4
        AND CONCAT(t2.e, t2.f) = CONCAT(t1.e, t1.f)
    ) THEN 1
    ELSE 0
END;

逻辑说明 #

1. 两种方案都会先按date排序，定位当前行的前4条数据
2. 检查这些前置行中是否存在与当前行CONCAT(e,f)相同的值
3. 前3行因为没有足够的前置行，result会被设为0，符合预期结果

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