oracle数据库去重，只保留一条数据 |
您所在的位置：网站首页 › plsql取第一条数据 › oracle数据库去重，只保留一条数据 |
oracle数据库去重，只保留一条数据
|
一、使用 Row_Number() Over(Partition By ... Order By ...) 为每个分组生成一个内部序号 第一步，根据需求查询重复数据，并为每个分组生成一个内部行号 Select t.*, Row_Number() Over(Partition By Visit_Id, Presc_Detail_Id, Rule_Code Order By t.Visit_Id) Su From Test t;
第二步，筛选出行号大于1的，即重复的多余的数据 Select * From (Select t.*, Row_Number() Over(Partition By Visit_Id, Presc_Detail_Id, Rule_Code Order By t.Visit_Id) Su From Test t) Where Su > 1;
第三步，删除重复数据，只保留1条 Delete From Test Where Id In (Select Id From (Select t.*, Row_Number() Over(Partition By Visit_Id, Presc_Detail_Id, Rule_Code Order By t.Visit_Id) Su From Test t) Where Su > 1);这种方式有个弊端，如果数据本身并没有每行的唯一标识id，删除重复数据还是非常困难的。
二、使用 ROWID 第一步，查找重复的记录 Select * From Test t Where (Visit_Id, Presc_Detail_Id, Rule_Code) In (Select Visit_Id, Presc_Detail_Id, Rule_Code From Test Group By Visit_Id, Presc_Detail_Id, Rule_Code Having Count(*) > 1) Order By Visit_Id, Presc_Detail_Id, Rule_Code;
第二步，查找表中多余的重复记录，不包含rowid最小的记录 Select * From Test t Where (Visit_Id, Presc_Detail_Id, Rule_Code) In (Select Visit_Id, Presc_Detail_Id, Rule_Code From Test Group By Visit_Id, Presc_Detail_Id, Rule_Code Having Count(*) > 1) And Rowid Not In (Select Min(Rowid) From Test Group By Visit_Id, Presc_Detail_Id, Rule_Code Having Count(*) > 1);
第三步，删除表中多余的重复记录，只保留rowid最小的记录 Delete From Test t Where (Visit_Id, Presc_Detail_Id, Rule_Code) In (Select Visit_Id, Presc_Detail_Id, Rule_Code From Test Group By Visit_Id, Presc_Detail_Id, Rule_Code Having Count(*) > 1) And Rowid Not In (Select Min(Rowid) From Test Group By Visit_Id, Presc_Detail_Id, Rule_Code Having Count(*) > 1);
|
【本文地址】
今日新闻 |
推荐新闻 |