除了在JPA标准API中的子查询中使用ORDER BY之外，还有什么其他选择？

Question

1. 项目（id,project_name)
2. 状态（id,id_project,status_name)

其中status包含项目已处于的所有状态。

假设我们想查询最新状态名称为“new”的所有项目。我提出的Sql查询是：

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id IN (
    SELECT TOP 1 sq.id from status sq
    WHERE q.id_project = sq.id_project 
    ORDER BY sq.id DESC )

CriteriaQuery<Project> q = criteriaBuilder.createQuery(Project.class);
Root<Status> qFrom       = q.from(Status.class);
Subquery<Integer> sq     = q.subquery(Integer.class);
Root<Status> sqFrom      = sq.from(Status.class);

sq.select(sqFrom.get(Status_.id))
  .where(criteriaBuilder.equal(sqFrom.get(Status_.project), qFrom.get(Status_.project))

在上面描述的场景中，为了得到所需的结果，对子查询进行排序还有什么替代方法？

Answer 1

可以使用select max而不是select TOP 1...ORDER BY...编写子查询：

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id = (
    SELECT MAX(sq.id) from status sq
    WHERE q.id_project = sq.id_project)

这也会更快，因为排序所有记录比查找最大值慢。正如已经发布的，它可以被翻译成一个CriteriaQuery。

不幸的是，条件查询不支持子查询中的排序-请参阅以下相关答案：

Query query = entityManager.createNativeQuery(
    "SELECT bar FROM foo WHERE bar IN (SELECT TOP 10 baz FROM quux ORDER BY quuux)");