MariaDB如何获取sval列最后一次变更值的首次出现记录

你原有的查询逻辑已经接近需求，只是末尾加了limit 1限制了仅返回第一条匹配记录，去掉该限制即可拿到所有最后一次sval变更后的符合要求的记录，同时可以补充边界兼容逻辑，避免全表sval都相同时查询返回空的问题：

with LastValue as (
  select t.sval
  from test t 
  order by t.date desc 
  limit 1
)
select t.*
from test t
where t.sval = (select sval from LastValue)
  and t.date > coalesce(
    (select max(tt.date) from test tt where tt.sval <> (select sval from LastValue)),
    '1000-01-01' -- 兼容所有记录sval都相同的边界场景
  )
order by t.date asc;

如果你的MariaDB版本支持窗口函数，还可以使用更简洁的写法，减少子查询次数，性能更优：

with marked_data as (
  select 
    *,
    first_value(sval) over(order by date desc) as latest_sval,
    lag(sval) over(order by date asc) as prev_sval
  from test
),
last_change as (
  select max(date) as change_date
  from marked_data
  where sval <> prev_sval
)
select *
from marked_data
where sval = latest_sval
  and date > coalesce((select change_date from last_change), '1000-01-01')
order by date asc;

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