PLSQL中去掉重复行的记录

【问题】

 

1. 我们经常会碰到的就是使用select语句查询数据库会出现相同的两条行记录，但是我们想要的结果就是其中一条（根据相应的条件进行排序）

select * from (select a.*,ROW_NUMBER() OVER (PARTITION BY a.SN ORDER BY a.CREATE_TIME) ROW_NUM 
from (SELECT * FROM table b where  b.create_time >= to_date('2018-11-25 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
 and b.create_time <= to_date('2018-11-28 17:59:59', 'yyyy-MM-dd hh24:mi:ss')) a)
where row_num = 1

ROW_NUMBER() OVER (PARTITION BY 'ID' ORDER BY 'DATE') ROW_NUM 是将相同的ID值按照序列1,2进行排序，在外层嵌套一个SELECT * FROM加上条件啊ROW_NUM = 1就能够得到想要的结果

删除重复记录时要特别注意：数据的唯一性，可通过字段唯一性来PARTITION BY a.SN 筛选 row_num>1 保留一条记录就可以

delete from table where m_freon_filling_log_id in (select id from (select a.*,ROW_NUMBER() OVER (PARTITION BY a.SN ORDER BY a.CREATE_TIME) ROW_NUM 
from (SELECT * FROM table b where b.create_time >= to_date('2018-11-25 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
 and b.create_time <= to_date('2018-11-28 17:59:59', 'yyyy-MM-dd hh24:mi:ss')) a)
where row_num > 1)