SQL Server去重复查询

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

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.[去重的列]
		)