row_number()在hql中的分区

发布时间：2020-12-13 21:24:57 所属栏目：Windows 来源：网络整理

导读：在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 " + "i

在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列附近. ..

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!