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屋!