HQL选择行出现多次的位置 [英] HQL Select where Row appears more than once

问题描述

我正在尝试恢复两列值出现多次的行。请注意，这不是优先事项。例如，在表格中。

  ID名称国家
 1 Dave英国
 2 Jim英镑
 3 Dave UK 
 4 Dave US 
 5 Jim US 
  

然后它会返回第1行和第3行，但没有其他的。这是因为Dave和UK的组合仅出现在这些行中。我努力实现一个HQL查询来获得这个。为了让事情继续下去，我试图用姓名进行分组：

 来自Runner r group by r.name having count（r。名称）> 1 
  

但我收到以下例外情况：

列RUNNER0_.ID必须在GROUP BY列表中;

我读到了为什么这是在逻辑上是必要的，所以现在我坚持我是如何实现这个功能的。

我使用的是Grails 2.4.4，JDK 7，Hibernate 4.3。 6.1

解决方案

您可以尝试使用 exists 与子查询一起搜索重复项。

 从Runner中选择r 
 r 
存在
（
 select rn 
其中rn.id<> r.id 
和rn.name = r.name 
和rn.country = r.country 
）
  

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

解决方案

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
)

这篇关于HQL选择行出现多次的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！