开发笔记:详述SQL中的distinct和row_number()over()的区别及用法

篇首语：本文由编程笔记#小编为大家整理，主要介绍了详述 SQL 中的 distinct 和 row_number() over() 的区别及用法相关的知识，希望对你有一定的参考价值。

1 前言

在咱们编写 SQL 语句操作数据库中的数据的时候&＃xff0c;有可能会遇到一些不太爽的问题&＃xff0c;例如对于同一字段拥有相同名称的记录&＃xff0c;我们只需要显示一条&＃xff0c;但实际上数据库中可能含有多条拥有相同名称的记录&＃xff0c;从而在检索的时候&＃xff0c;显示多条记录&＃xff0c;这就有违咱们的初衷啦&＃xff01;因此&＃xff0c;为了避免这种情况的发生&＃xff0c;咱们就需要进行“去重”处理啦&＃xff0c;那么何为“去重”呢&＃xff1f;说白了&＃xff0c;就是对同一字段让拥有相同内容的记录只显示一条记录。

那么&＃xff0c;如何实现“去重”的功能呢&＃xff1f;对此&＃xff0c;咱们有两种方式可以实现该功能。

• 第一种&＃xff0c;在编写 select 语句的时候&＃xff0c;添加 distinct 关键词&＃xff1b;
• 第二种&＃xff0c;在编写 select 语句的时候&＃xff0c;调用 row_number() over() 函数。

以上两种方式都可以实现“去重”功能&＃xff0c;那两者之间有何异同呢&＃xff1f;接下来&＃xff0c;作者将给出详细的说明。

2 distinct

在 SQL 中&＃xff0c;关键字 distinct 用于返回唯一不同的值。其语法格式为&＃xff1a;

SELECT DISTINCT 列名称 FROM 表名称


假设有一个表“CESHIDEMO”&＃xff0c;包含两个字段&＃xff0c;分别 NAME 和 AGE&＃xff0c;具体格式如下&＃xff1a;

观察以上的表&＃xff0c;咱们会发现&＃xff1a;拥有相同 NAME 的记录有两条&＃xff0c;拥有相同 AGE 的记录有三条。如果咱们运行下面这条 SQL 语句&＃xff0c;

/**
* 其中 PPPRDER 为 Schema 的名字&＃xff0c;即表 CESHIDEMO 在 PPPRDER 中
*/
select name from PPPRDER.CESHIDEMO


将会得到如下结果&＃xff1a;

观察该结果&＃xff0c;咱们会发现在以上的四条记录中&＃xff0c;包含两条 NAME 值相同的记录&＃xff0c;即第 2 条记录和第 3 条记录的值都为“gavin”。那么&＃xff0c;如果咱们想让拥有相同 NAME 的记录只显示一条该如何实现呢&＃xff1f;这时&＃xff0c;就需要用到 distinct 关键字啦&＃xff01;接下来&＃xff0c;运行如下 SQL 语句&＃xff0c;

select distinct name from PPPRDER.CESHIDEMO


将会得到如下结果&＃xff1a;

观察该结果&＃xff0c;显然咱们的要求得到实现啦&＃xff01;但是&＃xff0c;咱们不禁会想到&＃xff0c;如果将 distinct 关键字同时作用在两个字段上将会产生什么效果呢&＃xff1f;既然想到了&＃xff0c;咱们就试试呗&＃xff0c;运行如下 SQL 语句&＃xff0c;

select distinct name, age from PPPRDER.CESHIDEMO


得到的结果如下所示&＃xff1a;

观察该结果&＃xff0c;哎呀&＃xff0c;貌似没有作用啊&＃xff1f;她将全部的记录都显示出来了啊&＃xff01;其中 NAME 值相同的记录有两条&＃xff0c;AGE 值相同的记录有三条&＃xff0c;完全没有变化啊&＃xff01;但事实上&＃xff0c;结果就应该是这样的。因为当 distinct 作用在多个字段的时候&＃xff0c;她只会将所有字段值都相同的记录“去重”掉&＃xff0c;显然咱们“可怜”的四条记录并不满足该条件&＃xff0c;因此 distinct 会认为上面四条记录并不相同。空口无凭&＃xff0c;接下来&＃xff0c;咱们再向表“CESHIDEMO”中添加一条完全相同的记录&＃xff0c;验证一下即可。添加一条记录后的表如下所示&＃xff1a;

再运行如下的 SQL 语句&＃xff0c;

select distinct name, age from PPPRDER.CESHIDEMO


得到的结果如下所示&＃xff1a;

观察该结果&＃xff0c;完美的验证了咱们上面的结论。

此外&＃xff0c;有一点需要大家特别注意&＃xff0c;即&＃xff1a;关键字 distinct 只能放在 SQL 语句中所有字段的最前面才能起作用&＃xff0c;如果放错位置&＃xff0c;SQL 不会报错&＃xff0c;但也不会起到任何效果。

3 row_number() over()

在 SQL Server 数据库中&＃xff0c;为咱们提供了一个函数 row_number() 用于给数据库表中的记录进行标号&＃xff0c;在使用的时候&＃xff0c;其后还跟着一个函数 over()&＃xff0c;而函数 over() 的作用是将表中的记录进行分组和排序。两者使用的语法为&＃xff1a;

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


意为&＃xff1a;将表中的记录按字段 COLUMN1进行分组&＃xff0c;按字段 COLUMN2 进行排序&＃xff0c;其中

• PARTITION BY&＃xff1a;表示分组
• ORDER BY&＃xff1a;表示排序

接下来&＃xff0c;咱们还用表“CESHIDEMO”中的数据进行测试。首先&＃xff0c;给出没有使用 row_number() over() 函数时查询的结果&＃xff0c;如下所示&＃xff1a;

然后&＃xff0c;运行如下 SQL 语句&＃xff0c;

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


得到的结果如下所示&＃xff1a;

从上面的结果可以看出&＃xff0c;其在原表的基础上&＃xff0c;多了一列标有数字排序的列。那么反过来分析咱们运行的 SQL 语句&＃xff0c;发现其确实按字段 AGE 的值进行分组了&＃xff0c;也按字段 NAME 的值进行排序啦&＃xff01;因此&＃xff0c;函数的功能得到了验证。

接下来&＃xff0c;咱们就研究如何用 row_number() over() 函数实现“去重”的功能。通过观察上面的结果&＃xff0c;咱们可以发现&＃xff0c;如果以 NAME 分组&＃xff0c;以 AGE 排序&＃xff0c;然后再取每组的第一个记录或许就可以实现“去重”的功能啊&＃xff01;那么试试看&＃xff0c;运行如下 SQL 语句&＃xff0c;

/*
* 其中 rn 表示最后添加的那一列
*/
select * from
(select PPPRDER.CESHIDEMO.*, row_number() over(partition by name order by age desc) rn from PPPRDER.CESHIDEMO)
where rn &＃61; 1


运行后&＃xff0c;得到的结果如下所示&＃xff1a;

观察以上的结果&＃xff0c;我们发现&＃xff0c;哎呀&＃xff0c;数据“去重”的功能一不小心就被咱们实现了啊&＃xff01;不过很遗憾&＃xff0c;如果咱们细心的话&＃xff0c;会发现一个很不爽的事情&＃xff0c;那就是在执行以上 SQL 语句进行“去重”的时候&＃xff0c;有一条 NAME 值为“gavin”、AGE 值为“18”的记录被过滤掉了&＃xff0c;但是在现实生活会中&＃xff0c;同名不同年龄的事情太正常了。

4 总结

通过阅读及实践以上内容&＃xff0c;咱们已经知道了&＃xff0c;无论是用关键字 distinct 还是用函数 row_number() over() 都可以实现数据“去重”的功能。但是在实现使用的过程中&＃xff0c;咱们要特别注意两者的用法特点以及区别。

• 在使用关键字 distinct 的时候&＃xff0c;咱们要知道其作用于单个字段和多个字段的时候是有区别的&＃xff0c;作用于单个字段时&＃xff0c;其“去重”的是表中所有该字段值重复的数据&＃xff1b;作用于多个字段的时候&＃xff0c;其“去重”的表中所有字段&＃xff08;即 distinct 具体作用的多个字段&＃xff09;值都相同的数据。
• 在使用函数 row_number() over() 的时候&＃xff0c;其是按先分组排序后&＃xff0c;再取出每组的第一条记录来进行“去重”的&＃xff08;在本篇博文中如此&＃xff09;。当然&＃xff0c;在此处咱们还可以通过不同的限制条件来进行“去重”&＃xff0c;具体如何实现&＃xff0c;就需要大家自己去动脑思考啦&＃xff01;