HQL Select where Row appears more than once

问题:

I am attempting to bring back rows where two column values appear more than once. Note, this is not a priority. For example, in the table..

ID    Name    Country
1     Dave    UK
2     Jim     UK
3     Dave    UK
4     Dave    US
5     Jim     US

Then it would return row 1 and row 3, but none of the others. This is because the combination of "Dave" and "UK" only appears in those rows. I am struggling to implement a HQL query to obtain this. To get things going, I tried to group on just name:

from Runner r group by r.name having count(r.name) > 1

But I received the following exception:

Column "RUNNER0_.ID" must be in the GROUP BY list;

I read on why this is logically necessary, so now I'm stuck as to how I'm meant to implement this functionality.

I am using Grails 2.4.4, JDK 7, Hibernate 4.3.6.1

回答1:

You can try searching duplicates using exists with sub-query.

select r 
from Runner r 
where exists
(
    select rn
    from Runner rn
    where rn.id <> r.id 
    and rn.name = r.name
    and rn.country = r.country
)

回答2:

One approach is to use subqueries:

from Runner r
where (r.name, r.country) in 
   (select name, country from Runner group by name, country having count(*) > 1)