数据库常见面试题 —— 3. 数据库去重的方法

假设有一张学生信息表 StudentInfo ，里面有一些重复信息如下所示：

--建表
if object_id('StudentInfo','u') is not null drop table StudentInfo
go
create table StudentInfo (
    Student_ID   INT
    ,Student_Name varchar(20)
)
go
insert into StudentInfo
values
(1, 'Emma')
,(2, 'Jack')
,(3, 'Jane')
,(3,'Jane')
,(4, 'Bob')
,(5,'David'    )
,(6,'Isabel'  )
,(7,'Edward'   )
,(7,'Edward'  )
,(8,'Alice'   )
,(8,'Alice'  )
go

---

数据库去重的方式：
① Distinct
一般用于比较小的表进行去重。

select distinct Student_ID,Student_Name 
from StudentInfo

② Group by
主要用于分组统计，一般在聚合函数中使用。
例如，找出name 字段重复的记录，可以使用以下SQL 语句：

select Student_ID 
FROM StudentInfo 
group by Student_ID 
HAVING COUNT(Student_Name )>1 

③ 使用联合查询 （union）

select * from StudentInfo
union
select * from StudentInfo

④ row_number() over()

WITH CTE AS (  
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Student_ID ORDER BY Student_ID) as rnK 
  FROM StudentInfo   
)  
DELETE FROM CTE WHERE rn > 1;

⑤ 利用Oracle数据库自带的rowid属性，可以判断是否存在重复记录。
例如，对于a、b字段都重复的记录，只保留最新的一条，可以使用以下SQL语句：

select a, b, max(rowid) from test group by a, b。

删除操作则可以使用：

delete from test where rowid not in (select max(rowid) from test group by a, b)。