Oracle实现去重的两种方式总结

最新推荐文章于 2025-05-28 13:36:11 发布

原创最新推荐文章于 2025-05-28 13:36:11 发布 · 1.2w 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #oracle

数据库专栏收录该内容

44 篇文章

订阅专栏

本文探讨了在SQL查询中如何处理数据去重和使用row_number()函数进行行号分配的问题，提供了两种不同的解决方案：使用DISTINCT关键字和使用row_number() over()函数。在使用DISTINCT时，应注意与ORDER BY字段的关系；而row_number()函数则能实现分组内的唯一记录选择。示例查询涉及多表关联和XML数据提取。

业务场景
需要查询某数据，由于需要三张表关联查询，查询结果如下：
在这里插入图片描述
原始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;

一、distinct去重

select 
  distinct 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;

注意：DISTINCT后面要跟ORDER BY的字段，Oracle先执行 DISTINCT去重后，再使用ORDER BY进行排序的。所以如果在ORDER BY需要排序的字段，没有在 distinct 后的字段中，自然会抛错。
报错信息如下
在这里插入图片描述

二、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;