oracle去除重复数据

左手的ㄟ右手 2023-10-06 17:18 53阅读 0赞

1、删除所有重复的行

  1. delete from dis_zj_dz_back t
  2.  where t.rowid in (select rid
  3.                      from (select t1.rowid rid,
  4.                                   count(1) over(partition by t1.JOB_FLOW) rn
  5.                              from dis_zj_dz_back t1) t2
  6.                     where t2.rn > 1);

删除并备份

  1. create table dis_zj_dz_back2 as 
  2. select * from dis_zj_dz_back t
  3.  where t.rowid not in (select rid
  4.                      from (select t1.rowid rid,
  5.                                   count(1) over(partition by t1.JOB_FLOW) rn
  6.                              from dis_zj_dz_back t1) t2
  7.                     where t2.rn > 1);

2、删除重复数据并保留一条

  1. delete from dis_zj_dz_back t
  2.  where t.rowid in (select rid
  3.                      from (select t1.rowid rid,
  4.                                   row_number() over(partition by t1.job_flow order by create_time asc) rn
  5.                              from dis_zj_dz_back t1) t2
  6.                     where t2.rn > 1);

删除并备份

  1. create table dis_zj_dz_back2 as 
  2. select * from dis_zj_dz_back t
  3.  where t.rowid not in (select rid
  4.                      from (select t1.rowid rid,
  5.                                   row_number() over(partition by t1.job_flow order by create_time asc) rn
  6.                              from dis_zj_dz_back t1) t2
  7.                     where t2.rn > 1);

参考：https://blog.csdn.net/nayi_224/article/details/82020913

发表评论

表情：
评论列表 （有 0 条评论，53人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql去除重复数据

    mysql去除重复数据 在crm中，电话号码就往往就代表了一个客户，所以往往电话号码不能出现重复数据，下面就根据客户表client的phone字段去除重复数据:

    亦凉亦凉/ 0 赞/ 519 阅读