sql - 用于加入子查询的 JPA CriteriaBuilder

Question

我有这样的 SQL 语句:

SELECT * FROM person inner join (select max(validityId) as maxID from person group by personId) maxID on maxID.maxID = person.validityid;

所以它给了我personID的“不同”行:

如果我有这样的 table :

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    10      | 400     |
|    1       |    11      | 500     |
|    1       |    12      | 600     |
|    2       |    13      | 700     |
|    2       |    14      | 800     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

它会回来

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    12      | 600     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

现在我尝试通过 JPA CriteriaBuilder 来做到这一点。

我的第一个想法是子查询:

final CriteriaBuilder cb = this.em.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Long> subquery = cq.subquery(Long.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(validityIDSQ.get(Person_.validityId));
subquery.groupBy(validityIDSQ.get(Person_.personId));

cb.where(cb.in(root.get(Person_.validityId)).value(subquery));

但这会产生错误

 ERROR: column "person1_.validityid" must appear in the GROUP BY clause or be used in an aggregate function

这个怎么对？

马可

Answer 1

我认为解决方案比看起来更简单。您忘记包含 cb.max ()在 CriteriaBuilder 子查询中。以下代码执行您要查找的查询。

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Integer> subquery = cq.subquery(Integer.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(cb.max(validityIDSQ.get(Person_.validityID)));
subquery.groupBy(validityIDSQ.get(Person_.personID));

cq.where(cb.in(root.get(Person_.validityID)).value(subquery));

此代码将创建以下查询:

select
    person0_.id as id1_0_,
    person0_.personID as personID2_0_,
    person0_.validityID as validity3_0_,
    person0_.value as value4_0_
from
    person person0_
where
    person0_.validityID in (
            select
                max(person1_.validityID)
            from
                person person1_
            group by
                person1_.personID)

我认为您正在使用 Postgres。无 cd.max()它会生成您引用的错误，因为您使用 GroupBy 而不使用聚合函数。我在 Postgres 和 MySQL 上对其进行了测试。两者都具有魅力。