--去重-以其它条件去除重复信息---建议首先使用row——number来实现,因为无论是在数据库兼容,多种限制条件去重,sql逻辑复杂度,去重返回效率都是最好的
--最快的
delete from dfg where rowid in
(select r from
(select rowid r, row_number() over(partition by dt,name order by sal desc) m from dfg)
where m!=1)
--其次
delete
from 表名
where rowid not in
(select rowid
from (select rowid,max(rowid) over(partition by 分组元素) n--筛选重复唯一值
from (select 分组元素,筛选元素,max(筛选元素) over(partition by 分组元素) m--筛选出重复最大值
from 表名)
where 筛选元素 = m)
where rowid = n);
--耗费比较多多
delete
from dfg
where rowid in
(select rowid
from (select sal,max(sal) over(partition by dt,name) m--筛选出重复最大值
from dfg)
where sal != m
union
select rowid
from (select rowid,max(rowid) over(partition by dt,name,sal) n