SQL Server 之一列去重复操作

一列去重复后显示所有数据没有主键rowid的去重复查询

一列去重复后显示所有数据

select * from 表名 where 列2 in (select min([列2]) from [表名] group by [列1]); 列1 ：去重的字段； 列2：最好是自增长的int 类型的字段（如 rowid）；

没有主键rowid的去重复查询

1，创建伪列row_id

select ROW_NUMBER() over (order by [主键字段 ] desc) AS row_id, * from [表名] 主键字段可以是多列；

2,利用row_id去重复

select min(row_id) from ( select ROW_NUMBER() over (order by [主键字段 ] desc) AS row_id, * from [表名] ) t group by t.[去重复的列]

3，利用去重复后的row_id查询

select b.* from ( select ROW_NUMBER() over (order by [主键字段] desc) AS row_id, * from [表名] ) b where b.row_id in( select min(row_id) from ( select ROW_NUMBER() over (order by [主键字段] desc) AS row_id, * from [表名] t group by t.[去重的列] )