oracle怎么进行数据去重

2023-05-24 19:06| 来源: 网络整理| 查看: 265

去重方法：1、利用distinct关键字去重，语法“SELECT DISTINCT 字段名 FROM 表名;”；2、利用窗口函数row_number () over()去重；3、利用“group by”子句去重，语法“select 字段名 from 表名 group by 字段名;”；4、利用rowid进行伪列去重。

oracle怎么进行数据去重

本教程操作环境：Windows7系统、Oracle 11g版、Dell G3电脑。

业务场景

需要查询某数据，由于需要三张表关联查询，查询结果如下：

在这里插入图片描述原始SQL语句

SELECT D.ORDER_NUM AS "申请单号" , D.CREATE_TIME , D.EMP_NAME AS "申请人", (SELECT extractvalue(t1.row_data,'/root/row/FI13_wasteName') FROM dat_table_row t1 WHERE d.document_id = t1.document_id AND t1.table_id = 'dynamicRowsIdPTFLXX' ) AS "废料名称", (SELECT extractvalue(t1.row_data,'/root/row/FI13_units') FROM dat_table_row t1 WHERE d.document_id = t1.document_id AND t1.table_id = 'dynamicRowsIdPTFLXX' ) AS "单位", (SELECT extractvalue(t1.row_data,'/root/row/FI13_estimate') FROM dat_table_row t1 WHERE d.document_id = t1.document_id AND t1.table_id = 'dynamicRowsIdPTFLXX' ) AS "预估数量", (SELECT extractvalue(t1.row_data,'/root/row/FI13_stockRemoval') FROM dat_table_row t1 WHERE d.document_id = t1.document_id AND t1.table_id = 'dynamicRowsIdPTFLXX' ) AS "累计出库数量", (SELECT extractvalue(t1.row_data,'/root/row/FI13_receivingTime') FROM dat_table_row t1 WHERE d.document_id = t1.document_id AND t1.table_id = 'dynamicRowsIdCGYTX' ) AS "收购方收货时间", (SELECT extractvalue(t2.row_data,'/root/row/FI13_collectionTime') FROM dat_table_row t2 WHERE d.document_id = t2.document_id AND t2.table_id = 'dynamicRowsIdPTSJSKSJ' ) AS "实际收款时间" FROM dat_document d, dat_table_row dtr WHERE d.form_name ='FI14' AND d.document_id =dtr.document_id AND (D.DOCUMENT_STATUS != 'deleted' OR D.DOCUMENT_STATUS IS NULL ) --AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26' AND d.order_num = 'FI1420210708002' --FI1420210708002 ORDER BY d.CREATE_TIME DESC;登录后复制登录后复制

方法1：distinct去重

SELECT DISTINCT可以用来过滤结果集中的重复行，确保SELECT子句中返回指定的一列或多列的值是唯一的。

DISTINCT语句的语法如下：

SELECT DISTINCT column_1, column_2, ... FROM table_name;登录后复制

示例：

注意：DISTINCT后面要跟ORDER BY的字段，Oracle先执行 DISTINCT去重后，再使用ORDER BY进行排序的。所以如果在ORDER BY需要排序的字段，没有在 distinct 后的字段中，自然会抛错。

报错信息如下：

在这里插入图片描述

方法2：row_number() over()

语法格式

select * from (select A.*, row_number() over(partition by A.name1 order by A.name12 desc) rn from A) where rn = 1登录后复制

示例

select * from ( select d.order_num as "申请单号" , d.create_time , d.emp_name as "申请人", (select extractvalue(t1.row_data,'/root/row/FI13_wasteName') from dat_table_row t1 where d.document_id = t1.document_id and t1.table_id = 'dynamicRowsIdPTFLXX' ) as "废料名称", (select extractvalue(t1.row_data,'/root/row/FI13_units') from dat_table_row t1 where d.document_id = t1.document_id and t1.table_id = 'dynamicRowsIdPTFLXX' ) as "单位", (select extractvalue(t1.row_data,'/root/row/FI13_estimate') from dat_table_row t1 where d.document_id = t1.document_id and t1.table_id = 'dynamicRowsIdPTFLXX' ) as "预估数量", (select extractvalue(t1.row_data,'/root/row/FI13_stockRemoval') from dat_table_row t1 where d.document_id = t1.document_id and t1.table_id = 'dynamicRowsIdPTFLXX' ) as "累计出库数量", (select extractvalue(t1.row_data,'/root/row/FI13_receivingTime') from dat_table_row t1 where d.document_id = t1.document_id and t1.table_id = 'dynamicRowsIdCGYTX' ) as "收购方收货时间", (select extractvalue(t2.row_data,'/root/row/FI13_collectionTime') from dat_table_row t2 where d.document_id = t2.document_id and t2.table_id = 'dynamicRowsIdPTSJSKSJ' ) as "实际收款时间", row_number() over(partition by d.order_num order by d.create_time desc) rn from dat_document d, dat_table_row dtr where d.form_name ='FI14' and d.document_id =dtr.document_id and (d.document_status != 'deleted' or d.document_status is null ) --AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26' and d.order_num = 'FI1420210708002' --FI1420210708002 ) where rn = 1;登录后复制

查询结果在这里插入图片描述

方法3：group by

select 字段名 from 表名 group by 字段名;登录后复制

方法4：利用rowid（伪列去重）

select id,name,age from test t1 where t1.rowid in (select min(rowid) from test t2 where t1.name=t2.name and t1.age=t2.age);登录后复制

推荐教程：《Oracle教程》

以上就是oracle怎么进行数据去重的详细内容，更多请关注php中文网其它相关文章！

【本文地址】

公司简介

联系我们