MySQL如何查询指定列数值发生变更时对应的所有日期

方案1：MySQL 8.0及以上版本（推荐，性能最优） #

使用窗口函数LAG直接获取上一行的setting 2值做对比即可：

SELECT `date`, `setting 1`, `setting 2`
FROM (
    SELECT 
        *,
        LAG(`setting 2`) OVER (ORDER BY `date`) AS prev_setting2
    FROM your_table_name
) t
WHERE prev_setting2 IS NULL 
   OR `setting 2` != prev_setting2
ORDER BY `date`;

逻辑说明：

• 子查询里按date升序排序，用LAG函数取每行前一行的setting 2值存入prev_setting2字段
• 外层筛选条件：prev_setting2 IS NULL对应全表第一条数据（本身就是初始状态，默认属于变更节点），setting 2 != prev_setting2对应和前一行值不同的变更行
• 记得把your_table_name替换成你实际的表名，字段名带空格需要用反引号包裹避免语法错误

方案2：MySQL 5.x低版本兼容方案 #

低版本不支持窗口函数，可以用变量模拟行号后自连接对比：

SELECT t1.`date`, t1.`setting 1`, t1.`setting 2`
FROM (
    SELECT *, @rownum := @rownum + 1 AS rn
    FROM your_table_name, (SELECT @rownum := 0) init
    ORDER BY `date`
) t1
LEFT JOIN (
    SELECT *, @rownum2 := @rownum2 + 1 AS rn
    FROM your_table_name, (SELECT @rownum2 := 0) init
    ORDER BY `date`
) t2 ON t1.rn = t2.rn + 1
WHERE t2.`setting 2` IS NULL 
   OR t1.`setting 2` != t2.`setting 2`
ORDER BY t1.`date`;

两个方案执行后输出的结果和你给出的预期结果完全一致。

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