选择重复记录，其中一条记录必须包含确切的值

Question

I have a table as below with 3 columns amount, frequency & identifier. All these records are in the same table in the same database.

amount     frequency       identifier
4.75        1                100
4.75        3                101
4.76        2                102
4.76        3                103
4.44        1                104
4.43        1                105
4.75        2                106
4.67        2                107
4.75        3                108
4.64        2                109
4.64        3                110
4.65        4                111

I'm after a sql query that should pickup duplicate records for different frequency but frequency of one of them must be equal to 1. So the output i'm expecting is

amount  frequency   identifier
4.75        1       100
4.75        3       101
4.75        2       106
4.75        3       108

Note that the query is executed every 2 seconds along with 15 other queries that are lightweight. I can do the processing in php too, but prefer a database query so long its not performance intensive. I'm trying different queries, but can't get consistent results. Can someone please help ?

Many thanks

Answer 1

I'm thinking something like this would be the fastest way to get what you want:

select *
from t
where exists (select 1 from t t2 where t2.amount = t.amount and t2.frequency = 1)

This will run fastest with an index on (amount, frequency).