postgresql 如何有效地选择所有重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6504061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to select all duplicates efficiently
提问by nathanvda
I want to select all rows that have a value that already exists in the table. I did not find a better solution than
我想选择表中已存在值的所有行。我没有找到比这更好的解决方案
select *
from provisioning_requests tt
where code in (select code
from provisioning_requests tt2
where tt2.id <> tt.id)
This seems a bit naive. Does anybody have a better solution?
这似乎有点天真。有人有更好的解决方案吗？
回答by niktrs
select *
from provisioning_requests t1
join (select code from provisioning_requests group by code having count(*)>1) t2
ON t1.code = t2.code
OR
或者
select *
from provisioning_requests
WHERE code in (select code from provisioning_requests group by code having count(*)>1)
回答by Cyril Gandon
An Auto join do the job
自动加入完成这项工作
select tt.*
from provisioning_requests tt
INNER JOIN provisioning_requests tt2
ON tt.code = tt2.code
AND tt2.id <> tt.id
回答by Petar Ivanov
select t.*
from(
select *, count(1) over(partition by code) as cnt
from test
) as t
where t.cnt > 1
回答by Kirill Polishchuk
You can use operator exists, it produces better performance:
您可以使用 operator exists，它会产生更好的性能：
select *
from provisioning_requests tt
where exists
(
select 1
from provisioning_requests tt2
where tt2.id <> tt.id and tt2.code = tt.code
)
回答by Rez.Net
Perhaps using self join and also an index over Code column would make it perform better.
也许使用自连接和 Code 列上的索引会使其性能更好。
select pr1.* from provisioning_requests pr1
join provisioning_requests pr2 on pr1.code = pr2.code and pr1.id <> pr2.id
回答by Talha Ahmed Khan
you can monitor the row Codes by
您可以通过以下方式监控行代码
select code
from provisioning_requests tt
group by code
having count(code) > 1
回答by Sterex
How about using the distinct keyword?
如何使用不同的关键字？
SELECT col1, col2, col3, ..., DISTINCT(code) from provisioning_requests;
回答by rrapuya
how about :
怎么样 ：
SELECT *,COUNT(*) FROM provisioning_requests HAVING COUNT(*)>1
