冬眠与命令的区别 [英] Hibernate Distinct with order by

问题描述

任何人都可以告诉我如何在HQL查询中使用distinct与order。

我一直在寻找一个解决方案，但似乎无法找到确切的一个。


这是我的HQL查询

 从城市城市
中选择不同的城市
，其中city.id不为空
和upper（city.name）！ = upper（'Unknown'）
和city.state.id =：stateId 
按大写顺序（trim（city.name））
  

问题 - SELECT DISTINCT，ORDER BY表达式必须出现在选择列表中。

解决方案

通过不属于所选 distinct 列的列进行排序是没有意义的。

因为你没有加入集合，所以你的记录将会是不同的（至少PK会有所不同），你可以忽略不同的元素：

 从city city 
中选择不同的城市
其中city.id不为空
和upper（city.name）！= upper（'未知'）
和city .state.id =：stateId 
按上（tri m（city.name））
  

一般来说，当结果集中真的有重复时，想要消除它们，你可以用子查询来实现它：

 从城市城市
中选择城市
 （city.name））
 
这种方法的另一个好处是它的性能可能更好，因为 distinct  ing行通常是数据库中昂贵的操作。
 
can anyone tell me how to use distinct with order by in HQL query.
I've been looking for a solution but can't seem to find exact one.



This is my HQL query
select distinct city 
from City city 
  where city.id is not null 
    and upper(city.name) != upper('Unknown')  
    and city.state.id =:stateId 
order by upper(trim(city.name))
Problem -  SELECT DISTINCT, ORDER BY expressions must appear in select list.
解决方案
It does not make sense to order by a column which is not part of the selected distinct columns.

Since you are not joining with a collection, your records will be distinct anyway (at least PK will differ), you can just omit distinct:
select distinct city 
from City city 
  where city.id is not null 
    and upper(city.name) != upper('Unknown')  
    and city.state.id =:stateId 
order by upper(trim(city.name))
In general, when there really are duplicates in the result set and you want to eliminate them, you can achieve it with a subquery:
select city
from City city
  where city.id in (select c.id from City c join c.someCollection sc where ...)
order by upper(trim(city.name))
The other benefit of this approach is that it is probably better performance-wise, as distincting rows is usually an expensive operation in the database.

                        
这篇关于冬眠与命令的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！