SQL去重的三种方法汇总(distict、group by 和 row_number() over )

1. distinct

表userinfo 数据如下：

id	name	age	height
10	xiaogang	23	181
11	xiaoli	31	176
12	xiaohei	22	152
13	xiaogang	26	172
14	xiaoming	31	176

现在需要当前用户表不重复的用户名
select distinct name from userinfo

如结果（1）：
name 
xiaogang
xiaohei
xiaoli
xiaoming

可是我现在又想得到Id的值，改动如下

select distinct name,id from userinfo

如结果（2）

xiaogang 10
xiaoli 11
xiaohei 12
xiaogang 13
xiaoming 14

此时distinct同时作用了两个字段，即必须得id与name都相同的才会被排除

 

2. group by 

select name

from userinfo

groub by name 

运行上面3行sql的结果如上面distinct中的结果（1）

 

select  name,id

from userinfo 

groub by name ,id 

运行上面3行sql的结果如上面distinct的结果（2）

3. SQL Server数据库的 row_number() over 

SQL Server 通过Row_Number 函数给数据库表的记录进行标号，在使用的时候后面会跟over 子句，而over 子句主要用来对表中的记录进行分组和排序的。

语法如下:

ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

1:Partition BY 用来分组

2：Order by 用来排序

接下来用 row_number（） over 进行去重。首先用name 进行分组，id进行排序。

具体SQL 语句如下

SELECT * FROM (
select *,ROW_NUMBER() over(partition by name order by id desc) AS rn from userinfo ) AS u WHERE u.rn=1

 

结果如下

id  name   age height rn

13 xiaogang 26 172 1
12 xiaohei 22 152 1
11 xiaoli 31 176 1
14 xiaoming 31 176 1

通过使用 row_number over 子句就能将所有的列展示出来，同时进行去重。

 

4.思考

 

distinct 和group by 的区别：

（1）distinct常用来查询不重复记录的条数：count(distinct name)，group by 常用它来返回不重记录的所有值。

（2）在使用group by 分组后，在select中可以选择分组字段，和非分组字段的函数值，如 max()、min()、sum、count()等

 

distinct 和row_number over()区别:

（1）distinct 和 row_number over 都可以实现去重功能，而distinct 作用于当行的时候，其"去重" 是去掉表中字段所有重复的数据，作用于多行的时候是，其"去重"所有字段都相同的数据。

（2）在使用row_number over 子句时候是先分组，然后进行排序，再取出每组的第一条记录"去重"