分析:
---partition by column_l 相当于group by column_l
---rs_num 相当于给重复的数据排序(不重复的数据rs_num=1) ,column_l指的是去重的字段
sql:
select t1.* from (
select t.*,row_number() over(partition by column_l order by column_aq asc) as rs_num from table t)t1 where t1.rs_num=1;
----t1表得到的结果是:
---去重后的结果就是 :t1表中rs_num=1 的数据
第二种去重办法:(数据量大时不建议使用)
保留了rowid最小的值
DELETE from table WHERE (column_l) IN
( SELECT column_l FROM table GROUP BY column_l HAVING COUNT(column_l) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM table GROUP BY column_l HAVING COUNT(*) > 1);