SQL查询去重帮助
SQL查询去重帮助
提问于 2009-10-12 11:35:39
我需要从下表中删除半重复记录
ID PID SCORE
1 1 50
2 33 20
3 1 90
4 5 55
5 7 11
6 22 34对于存在的任何重复PID，我希望删除最低得分记录。在上面的示例中，ID 1将被删除。我试图想出一种不使用循环的方法，但我真的很挣扎。
任何帮助都将不胜感激。
谢谢
回答 4
Stack Overflow用户
回答已采纳
发布于 2009-10-12 11:44:00
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.maxIdStack Overflow用户
发布于 2009-10-12 11:37:01
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY pid ORDER BY score) AS rn
FROM mytable
)
DELETE
FROM q
WHERE rn = 1要保留没有重复项的结果：
WITH q AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY pid ORDER BY score) AS rn,
COUNT(*) OVER (PARTITION BY pid) AS cnt
FROM mytable
)
DELETE
FROM q
WHERE rn = 1
AND cnt > 1Stack Overflow用户
发布于 2009-10-12 12:29:57
试试这个..。
declare @tt table(id int, pid int,score int)
insert into @tt
select 1,1,50 union all
select 2,33,50 union all
select 8,33,80 union all
select 3,1,90 union all
select 4,5,50 union all
select 5,5,10 union all
select 6,6,10 union all
select 7,6,50
---------
delete from @tt where id in (
select t1.id from @tt t1 inner join
(
select MIN(score) tScore,pid tPid from @tt where pid in
(select pid from @tt group by pid having count (pid) > 1) group by pid
) t2 on t1.pid=t2.tPid and t1.score=t2.tScore)
select * from @tt页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1554060
复制相关文章
相似问题
腾讯云开发者
