On this page

SQL Server

SQLServer删除重复记录

有时候业务数据表中直接查询得出的数据可能含有重复的记录，此时就需要我们去过滤一些重复的数据。

根据单个字段（user_id）来进行判断

select * from users where user_id in(select user_id from users group by user_id having count(user_id) > 1)
/** or */
select * from users where user_id in(select user_id from users group by user_id having count(user_id > 1) and row_id not in(select min(row_id) from users group by user_id having count(user_id) > 1)

根据多个字段进行筛选

select * from users as a where (a.user_id,a.phone in (select user_id, phone from users group by user_id, phone having count(1) > 1))
/** or */
select * from users as a where(a.user_id, a.phone in (select user_id, phone from users group by user_id, phone having count(1) > 1) and row_id not in(select min(row_id) from users group by user_id,phone having count(1) > 1)