SQL查询重复删除帮助 [英] SQL Query Duplicate Removal Help

问题描述

我需要从下表中删除半重复记录

I need to remove semi duplicate records from the following table

ID      PID      SCORE
1       1        50
2       33       20
3       1        90
4       5        55
5       7        11
6       22       34

对于存在的任何重复的PID，我想删除最低的得分记录。在上面的示例中，ID 1将被删除。我试图想出一个这样做，而不使用循环，但真的很难。

For any duplicate PID's that exist I want to remove the lowest scoring record. In the example above ID 1 would be remove. I'm trying to come up with a way of doing this without using loops but am really struggling.

任何帮助将不胜感激。

谢谢

推荐答案

    DELETE t.* 
    FROM Table1 t 
    JOIN (SELECT pid, MIN(score) minScore, MAX(id) maxId
            FROM Table1
        GROUP BY pid) t1 
    ON t.pid = t1.pid 
   AND t.score = t1.minScore 
   AND t.id < t1.maxId

这篇关于SQL查询重复删除帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！