QueryDSL窗口函数 [英] QueryDSL Window functions

问题描述

如何使用窗口函数编写查询并选择QueryDSL中的所有字段？
在文档中有一个这样的例子：

How can I write a query using window functions and selecting all fields in QueryDSL? In the docs there is an example like this:

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

但我需要生成如下查询：

but I need to generate a query like:

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

可以用JPAQuery做到吗？

And is it possible to do it with JPAQuery?

推荐答案

JPAQuery仅支持JPQL的表现力，因此不支持窗口函数，但是分页应该可以使用

JPAQuery supports only the expressivity of JPQL, so window functions are not supported, but paging should work using

query.from(employee).orderBy(employee.id).limit(1)

如果你需要使用窗口函数，你需要employee.name和employee.id，这应该可以工作

In case you need to use window functions and you need employee.name and employee.id out this should work

NumberExpression<Long> rowNumber = SQLExpressions.rowNumber()
    .over()
    .partitionBy(employee.name)
    .orderBy(employee.id).as("rowNumber");

query.select(employee.name, employee.id)
    .from(SQLExpressions.select(employee.name, employee.id, rowNumber)
                        .from(employee).as(employee))
    .where(Expressions.numberPath(Long.class, "rowNumber").eq(1L))
    .fetch();

这篇关于QueryDSL窗口函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！