mysql – SQL Server:如何避免重复数据?

enter image description here

我想查询上面的图片.

左图是原始数据,右图是查询数据.

select distinct ID, Nickname, Revision 
from test_table

此查询不显示上面的图片.

如何避免重复数据?

如果是SQL Server,则在子查询中使用窗口函数ROW_NUMBER:

select t.id, t.nickname, t.revision
from (
    select t.*, row_number() over (
            partition by t.id order by t.revision desc
            ) rn
    from your_table t
    ) t
where rn = 1;

或者使用与ROW_NUMBER关联的TOP:

select top 1 with ties *
from your_table
order by row_number() over (
        partition by id order by revision desc
        )

如果MySQL:

select t.*
from your_table t
inner join (
    select id, MAX(revision) revision
    from your_table
    group by id
    ) t1 on t.id = t1.id
    and t.revision = t1.revision;
相关文章
相关标签/搜索