Oracle 去除重复记录
在数据库操作中,有时候会出现重复记录的情况,这个时候需要去除这些重复的记录。针对 Oracle 数据库,有以下几种方式可以去除重复记录。
1.使用DISTINCT关键字
DISTINCT 关键字可以用来返回一列中不同的值。具体使用方式如下:
SELECT DISTINCT 列名 FROM 表名;
例如,我们有一个学生表(student)如下:
学生编号 | 学生姓名 | 学生年龄 | 所在城市 |
---|---|---|---|
1 | 张三 | 22 | 北京 |
2 | 李四 | 21 | 上海 |
3 | 王五 | 23 | 北京 |
4 | 张三 | 22 | 北京 |
5 | 赵六 | 24 | 天津 |
如果我们想要查询学生表中所有不同的城市名称,可以使用以下 SQL 语句:
SELECT DISTINCT 所在城市 FROM student;
查询结果为:
所在城市 |
---|
北京 |
上海 |
天津 |
注意,如果 SELECT 语句中包含多个列名,则 DISTINCT 关键字将会作用于所有列,返回的结果中将会去除所有列的重复记录。
2.使用GROUP BY关键字
GROUP BY 关键字可以用来按指定的列对记录进行分组,并且可以对分组后的记录进行一些聚合操作(如求和、平均等)。具体使用方式如下:
SELECT 列名, 聚合函数 FROM 表名 GROUP BY 列名;
例如,我们有一个订单表(orders)如下:
订单编号 | 客户名称 | 订单金额 | 下单时间 |
---|---|---|---|
1 | 张三 | 1000 | 2022-01-01 |
2 | 李四 | 2000 | 2022-01-02 |
3 | 张三 | 1500 | 2022-01-03 |
4 | 王五 | 3000 | 2022-01-04 |
5 | 李四 | 2500 | 2022-01-05 |
如果我们想要查询每个客户的订单总金额,可以使用以下 SQL 语句:
SELECT 客户名称, SUM(订单金额) AS 订单总金额 FROM orders GROUP BY 客户名称;
查询结果为:
客户名称 | 订单总金额 |
---|---|
张三 | 2500 |
李四 | 4500 |
王五 | 3000 |
注意,GROUP BY 关键字只能用在 SELECT 语句中,而且 SELECT 语句中必须包含 GROUP BY 后面出现的列名或者聚合函数。
3.使用ROWID伪列
ROWID 是 Oracle 数据库中的一种特殊的伪列,用来表示一条记录在表中的物理地址。可以使用 ROWID 伪列去除重复记录。具体使用方式如下:
SELECT * FROM 表名 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 表名 GROUP BY 列名);
例如,我们有一个部门表(dept)如下:
部门编号 | 部门名称 | 所在城市 |
---|---|---|
1 | 研发部 | 北京 |
2 | 市场部 | 上海 |
3 | 人事部 | 北京 |
4 | 研发部 | 北京 |
如果我们想要去除部门表中重复的部门名称记录,可以使用以下 SQL 语句:
SELECT * FROM dept WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM dept GROUP BY 部门名称);
查询结果为:
部门编号 | 部门名称 | 所在城市 |
---|---|---|
1 | 研发部 | 北京 |
2 | 市场部 | 上海 |
3 | 人事部 | 北京 |
注意,使用 ROWID 伪列去除重复记录只能保留第一条记录,如果希望保留最后一条记录,则需要把 MAX 改为 MIN。
4.使用ROW_NUMBER()函数
ROW_NUMBER() 函数是 Oracle 数据库中的一种窗口函数,用来为结果集中的每一行分配一个唯一的数字,可以使用 ROW_NUMBER() 函数去除重复记录。具体使用方式如下:
WITH CTE AS (
SELECT 列名, ROW_NUMBER() OVER (PARTITION BY 列名 ORDER BY 列名) AS 行号 FROM 表名
)
SELECT * FROM CTE WHERE 行号 = 1;
其中,PARTITION BY
用来指定分组列名,ORDER BY
用来指定排列顺序。
例如,我们有一个订单表(orders)如下:
订单编号 | 客户名称 | 订单金额 | 下单时间 |
---|---|---|---|
1 | 张三 | 1000 | 2022-01-01 |
2 | 李四 | 2000 | 2022-01-02 |
3 | 张三 | 1500 | 2022-01-03 |
4 | 王五 | 3000 | 2022-01-04 |
5 | 李四 | 2500 | 2022-01-05 |
如果我们想要查询每个客户的第一笔订单记录,可以使用以下 SQL 语句:
WITH CTE AS (
SELECT 客户名称, 订单编号, ROW_NUMBER() OVER (PARTITION BY 客户名称 ORDER BY 下单时间) AS 行号 FROM orders
)
SELECT * FROM CTE WHERE 行号 = 1;
查询结果为:
客户名称 | 订单编号 | 行号 |
---|---|---|
张三 | 1 | 1 |
李四 | 2 | 1 |
王五 | 4 | 1 |
注意,使用 ROW_NUMBER() 函数去除重复记录可以保留每个分组的第一条记录,如果希望保留每个分组的最后一条记录,则需要把 ORDER BY
中的排序顺序改为倒序。
总结
以上就是 Oracle 数据库去除重复记录的几种方式。不同的情况下,适用的方式也不同。使用 DISTINCT 关键字适用于查询单列去重的情况;使用 GROUP BY 关键字适用于对分组后的数据进行聚合操作的情况;使用 ROWID 伪列适用于只保留第一条或最后一条记录的情况;使用 ROW_NUMBER() 函数适用于保留每个分组的第一条或最后一条记录的情况。