202、SQL中distinct 和 row_number() over() 的区别及用法及数据库去重

有两种方式可以实现去重功能：

第一种，在编写 select 语句的时候，添加 distinct 关键词；

第二种，在编写 select 语句的时候，调用 row_number() over() 函数。

A、distinct

语法格式: SELECT DISTINCT 列名称 FROM 表名称

当 distinct 作用在多个字段的时候，她只会将所有字段值都相同的记录“去重”掉

如：select distinct name from PPPRDER.CESHIDEMO

　　select distinct name, age from PPPRDER.CESHIDEMO

 

A、row_number() over()

在 SQL Server 数据库中，提供了一个函数 row_number() 用于给数据库表中的记录进行标号。在使用的时候，

其后还跟着一个函数 over()，而函数 over() 的作用是将表中的记录进行分组和排序。两者使用的语法为：ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

PARTITION BY：表示分组ORDER BY：表示排序

row_number()从1开始，为每一条分组记录返回一个数字

ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

1、SQL脚本：

　select PPPRDER.CESHIDEMO.*, row_number() over(partition by age order by name desc) from PPPRDER.CESHIDEMO

在原表的基础上，多了一列标有数字排序的列

2、SQL脚本：
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee 将会多展示出一列列名为 rank 

需求：根据部门分组，显示每个部门的工资等级

empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

3、用 row_number() over() 函数实现“去重”的功能：如果以 NAME 分组，以 AGE 排序，然后再取每组的第一个记录或许就可以实现“去重”的功能
SQL 语句:

/*

* 其中 rn 表示最后添加的那一列

*/

select * from

　　(select PPPRDER.CESHIDEMO.*, row_number() over(partition by name order by age desc) rn from PPPRDER.CESHIDEMO)

where rn = 1

与其比较，原表未去重前为：

 

 

4、对ROW_NUMBER()函数的简单分页应用

教师工号      教师姓名        教学课程编号       教学课程      字段编号
----------      -------------       ---------------      ------------    ----------
200401          赵子丹                1001              ASP.NET           1
200402          李林                   1001              ASP.NET           2
200403          黄建波                1001              ASP.NET           3
200404          易明                   1002                 JAVA             4
200405          张理                   1002                 JAVA             5
200406          吴平                   1002                 JAVA             6

SELECT * FROM 

　　(select *,row_number() OVER(ORDER BY  教师工号) AS 字段编号 from 教师表) a

WHERE a.字段编号 BETWEEN 5 AND 10

根据上面的sql语句可以查询出5-10的记录

总结：

在使用关键字 distinct 的时候，其作用于单个字段和多个字段的时候是有区别的，

作用于单个字段时，其“去重”的是表中所有该字段值重复的数据；作用于多个字段的时候，其“去重”的表中所有字段（即 distinct 具体作用的多个字段）值都相同的数据。

在使用函数 row_number() over() 的时候，其是按先分组排序后，再取出每组的第一条记录来进行“去重”的。（在本篇博文中如此）。当然，在此处还可以通过不同的限制条件来进行“去重”。