[SQL] 筛选重复数据

先来看看表里的数据

场景：表table1，字段id，name，pass，无主键，无唯一字段

需求：筛选/删除 重复的每一对 name+pass

 

先使用PARTITION BY子句分段+ROW_NUMBER()函数对每段分行

SELECT *,ROW_NUMBER() OVER (PARTITION BY name,pass ORDER BY (SELECT 0)) AS iRow FROM table1 ORDER BY name,pass

然后只需要把iRow里值不为1的列提取出来即可

最终代码：

SELECT * FROM(
       SELECT *,ROW_NUMBER() OVER (PARTITION BY name,pass ORDER BY (SELECT 0)) AS iRow FROM table1
) tempTB WHERE iRow != 1 ORDER BY name,pass