可以使用窗口函数：

select t.*
from (select t.*, row_number() over (partition by productid order by keyid desc) as seqnum
      from (select t.*,
                   lag(status) over (partition by productid order by keyid) as prev_status
            from t
           ) t
      where prev_status is null or prev_status <> status
     ) t
where seqnum = 1;

最里面的子查询将每个值与其前一个值进行比较。中间子查询枚举状态发生更改的产品的每一行。外部查询筛选以获取最新的查询。

如果您的数据库不支持窗口函数，那么逻辑就要复杂一些。
作为初学者，请考虑下面的查询，它为您提供了第一个 idkey 具有最新 status 每 product_id :

select product_id, min(keyid)
from mytable t
where not exists (
    select 1 
    from mytable t1 
    where t1.product_id = t.product_id and t1.status <> t.status and t1.keyid > t.keyid
)
group by product_id

这将返回：

product_id | min(keyid)
---------: | ---------:
       598 |         13
       456 |         15

然后可以使用此结果集筛选表：

select t.*
from mytable t
where t.keyid in (
    select min(keyid)
    from mytable t
    where not exists (
        select 1 
        from mytable t1 
        where t1.product_id = t.product_id and t1.status <> t.status and t1.keyid > t.keyid
    )
    group by product_id
)

结果：

product_id | name | status | user | keyid
---------: | :--- | -----: | :--- | ----:
       598 | prdd |      2 | kate |    13
       456 | prrr |      3 | john |    15

db小提琴演示