HQL中的分区上的row_number() [英] row_number() over partition in hql
问题描述
hql中的分区上的row_number()等价于什么 我在hql中有以下查询:
What is the equivalent of row_number() over partition in hql I have the following query in hql:
select s.Companyname, p.Productname, sum(od.Unitprice * od.Quantity - od.Discount) as SalesAmount FROM OrderDetails as od inner join od.Orders as o inner join od.Products as p " +
"inner join p.Suppliers as s" +
" where o.Orderdate between '2010/01/01' and '2014/01/01' GROUP BY s.Companyname,p.Productname"
我想按s.Companyname
进行分区,其中RowNumber <= n
.
I want to do partition by s.Companyname
where RowNumber <= n
.
推荐答案
据我所知,在 JPQL .我建议使用原生SQL 查询在这种情况下:
As far as I know you cannot use row_number()
neither in HQL nor in JPQL. I propose to use a native SQL query in this case:
@PersistenceContext
protected EntityManager entityManager;
...
String sQuery = "SELECT q.* FROM (" +
"SELECT s.company_name, " +
"p.product_name, " +
"sum(od.unit_price * od.quantity - od.discount) as SalesAmount, " +
"row_number() OVER (partition by s.company_name) as rn " +
"FROM OrderDetails od " +
"INNER JOIN Orders o ON o.id = od.order_id " +
"INNER JOIN Products p ON p.id = od.product_id " +
"INNER JOIN Suppliers s ON s.id = p.supplier_id " +
"WHERE o.order_date between '2010/01/01' and '2014/01/01') as q " +
"WHERE rn <= :n";
List<ResultDbo> results = new ArrayList<>();
Query query = entityManager.createNativeQuery(sQuery);
query.setParameter("n", n);
List<Object[]> resultSet = query.getResultList();
for (Object[] resultItem : resultSet) {
ResultDbo result = new ResultDbo();
result.setCompanyName((String) resultItem[0]);
result.setProductName((String) resultItem[1]);
result.setSalesAmount((String) resultItem[2]);
results.add(result);
}
如果您尝试在HQL中使用OVER()
,几乎可以肯定会得到一些验证异常,例如java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: OVER near line 1, column 42 ...
If you ever try to use OVER()
in HQL you'll almost certainly get some validation exception like java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: OVER near line 1, column 42 ...
这篇关于HQL中的分区上的row_number()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!