sql删除数据库重复字段的行

要保留的数据：

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer --分组 ORDER BY p.total DESC) AS rk FROM PURCHASES p) SELECT s.* FROM summary s WHERE s.rk = 1 --1表示分组后分组结果第一行

稍微修改之后的删除：

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer --customer字段重复了，则根据其分组
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)

delete from PURCHASES where customer not in(
SELECT s.*
  FROM summary s  
 WHERE s.rk = 1 --1表示分组后分组结果第一行
)

此方法对uniqueidentifier（Guid）有效哦！

参考自：http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

转载于:https://www.cnblogs.com/huangsheng/p/4717438.html