how to select all duplicates efficiently
I want to select all rows that have a value that al开发者_开发百科ready 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?
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)
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
select t.*
from(
select *, count(1) over(partition by code) as cnt
from test
) as t
where t.cnt > 1
You can use operator exists
, it produces better performance:
select *
from provisioning_requests tt
where exists
(
select 1
from provisioning_requests tt2
where tt2.id <> tt.id and tt2.code = tt.code
)
How about using the distinct keyword?
SELECT col1, col2, col3, ..., DISTINCT(code) from provisioning_requests;
Perhaps using self join and also an index over Code column would make it perform better.
select pr1.* from provisioning_requests pr1
join provisioning_requests pr2 on pr1.code = pr2.code and pr1.id <> pr2.id
you can monitor the row Codes by
select code
from provisioning_requests tt
group by code
having count(code) > 1
how about :
SELECT *,COUNT(*) FROM provisioning_requests HAVING COUNT(*)>1
精彩评论