• 技术文章 >数据库 >Oracle

    oracle怎么进行数据去重

    青灯夜游青灯夜游2023-01-04 14:42:25原创162

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

    本教程操作环境: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;

    示例:

    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后面要跟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中文网其它相关文章!

    声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。
    专题推荐:oracle
    上一篇:oracle定时任务定时无效的原因分析与解决 下一篇:自己动手写 PHP MVC 框架(40节精讲/巨细/新人进阶必看)

    相关文章推荐

    • 归纳总结Oracle查询执行计划• oracle怎么查看表空间• oracle中有动态语句吗• oracle外键失效怎么办
    1/1

    PHP中文网