在hql中,row_number()相当于分区的含义
我在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.
据我所知,你不能在
HQL和
JPQL中使用row_number().我建议在这种情况下使用
native SQL查询:
@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:意外令牌:在第1行第42列附近. ..