是否有一个SQL只显示从上一行更改的所有列的值?

select nullif(name, lag(name) over (order by mod_date)) as name,
       mod_date,
       nullif(create_user_id, lag(create_user_id) over (order by mod_date)) as create_user_id,
       nullif(is_active, lag(is_active) over (order by mod_date)) as is_active,
       nullif(other_column, lag(name) over (order by mod_date)) as other_column
from t
order by mod_date;

您可以使用 information_schema.columns

这假定列值不正确 NULL --如果是,那么你的结果无论如何都是模糊的。

select name
from (select h.*,
    lag(name) over main_window as lag_name,
    lag(create_user_id) over main_window as prev_user_id,
    lag(is_active) over main_window as prev_is_active,
    lag(other_column) over main_window as prev_other_column
from history h
window main_window as (partition by h.id order by h.mod_date)
) h
where name <> lag_name
or create_user_id <> prev_user_id
or is_active <> prev_is_active
or other_column <> prev_other_column

对于每一列,使用如下表达式

CASE WHEN lag(col) OVER (ORDER BY mod_date) IS DISTINCT FROM col THEN col END

如果相邻行的列值相同,则将生成NULL。