QueryDSL窗口函数

Question

query.from(employee)
.list(SQLExpressions.rowNumber()
    .over()
    .partitionBy(employee.name)
    .orderBy(employee.id));

SELECT * FROM 
  (SELECT employee.name, employee.id, row_number() 
    over(partition BY employee.name
    ORDER BY employee.id)
  FROM employee) AS sub
WHERE row_number = 1

Answer 1

正如@timo编写的那样，JPQL(JPA2.1版本）不支持窗口函数(rank、row_number)，因此也不支持JPAQuery(QueryDsl JPA 4.1.4)。

但是，您可以重写查询，这样is就不会使用rank over（）：

select a.* from employees a
where
(
    select count(*) from employees b
    where 
       a.department = b.department and
       a.salary <= b.salary
) <= 10
order by salary DESC

这是由JPAQuery支持的，它可能是这样的。

final BooleanBuilder rankFilterBuilder = 
    new BooleanBuilder(employee.department.eq(employee2.department));
rankFilterBuilder.and(employee.salary.loe(employee2.salary));

query.from(employee)
.where(JPAExpressions.selectFrom(employee2)
            .where(rankFilterBuilder)
            .select(employee2.count())
            .loe(10))
.orderBy(employee.salary);