如何编写按日期排序筛选Column A变为1的Select查询语句

核心逻辑 #

我们需要对比按Date升序排序后的相邻两行Column A的值：当前行Column A为1，且前一行Column A不为1的行，即为符合要求的「刚变更为1」的记录，同时兼容第一条记录本身就是Column A=1的场景。

标准SQL实现（适用于支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

使用窗口函数LAG获取当前行的上一行Column A值，再做筛选即可：

WITH change_log_with_prev AS (
    SELECT
        *,
        -- 按Date升序排序，取上一行的Column A值
        LAG(`Column A`) OVER (ORDER BY `Date` ASC) AS prev_column_a
    FROM 你的表名 -- 替换为实际表名
)
SELECT `Column A`, `Column B`, `Date`
FROM change_log_with_prev
WHERE 
    -- 当前行Column A为1
    `Column A` = 1
    -- 上一行Column A不为1，或上一行不存在（首行就是1的场景）
    AND (prev_column_a != 1 OR prev_column_a IS NULL);

低版本数据库兼容实现（不支持窗口函数的场景，如MySQL 5.x） #

可以用自关联的方式匹配相邻行：

SELECT t1.*
FROM 你的表名 t1
LEFT JOIN 你的表名 t2
  -- 匹配t1的前一行记录（时间小于t1，且没有其他记录时间介于两者之间）
  ON t2.`Date` < t1.`Date`
  AND NOT EXISTS (
      SELECT 1 FROM 你的表名 t3
      WHERE t3.`Date` < t1.`Date` AND t3.`Date` > t2.`Date`
  )
WHERE
  t1.`Column A` = 1
  AND (t2.`Column A` != 1 OR t2.`Column A` IS NULL);

注意事项 #

如果存在多条记录Date字段完全相同的情况，建议在排序规则中补充唯一排序字段（如自增主键ID），保证相邻行匹配的准确性，示例排序规则：ORDER BY Date ASC, id ASC

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