本文介绍了PSQL获得重复行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的表 -
id object_id product_id 1 1 1 2 1 1 4 2 2 6 3 2 7 3 2 8 1 2 9 1 1我想删除除这些以外的所有行 -
I want to delete all rows except these-
1 1 1 4 2 2 6 3 2 9 1 2基本上有重复的,我想删除它们,但是保持一个副本不变。
Basically there are duplicates and I want to remove them but keep one copy intact.
这是最有效的方式?
推荐答案如果这是一次性的话,那么你可以简单地确定要保留的记录如下:
If this is a one-off then you can simply identify the records you want to keep like so:
SELECT MIN(id) AS id FROM yourtable GROUP BY object_id, product_id;你想检查这个工作,然后再做下一件事,实际上会抛出记录。要实际删除您所做的重复记录:
You want to check that this works before you do the next thing and actually throw records out. To actually delete those duplicate records you do:
DELETE FROM yourtable WHERE id NOT IN ( SELECT MIN(id) AS id FROM yourtable GROUP BY object_id, product_id );MIN(id)显然总是返回一组( object_id , product_id )的最低ID的记录。根据需要进行更改。
The MIN(id) obviously always returns the record with the lowest id for a set of (object_id, product_id). Change as desired.
更多推荐
PSQL获得重复行
发布评论