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

约定不等于承诺〃 2022-10-20 01:29 221阅读 0赞

业务场景  
需要查询某数据，由于需要三张表关联查询，查询结果如下：  
![在这里插入图片描述][20210712161820118.png]  
原始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 后的字段中，自然会抛错。  
报错信息如下  
![在这里插入图片描述][20210712160632136.png]

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

查询结果  
![在这里插入图片描述][20210712161910668.png]

**参考文章**  
https://www.cnblogs.com/fina1ly/p/9509795.html  
https://blog.csdn.net/deniro\_li/article/details/79024099

[20210712161820118.png]: /images/20221004/e9b83c52b1c14983bc1b5ed85201d360.png
[20210712160632136.png]: /images/20221004/36da055477ec4640bcbe73e30592a986.png
[20210712161910668.png]: /images/20221004/f332116076614703891f600a724e06eb.png