java - 如何使用 JPA 存储库使用同一个表进行联接？

Question

我用 MySQL 编写了这个查询，它有效:

select c.*
from macchine.check_list c
join (select pratica, documentazione, max(versione) as versione
      from macchine.check_list
      group by pratica, documentazione) c2
  on c.pratica = c2.pratica
  and c.documentazione = c2.documentazione
  and c.versione = c2.versione;

然后我尝试使用 JPA 存储库将此查询移到我的 Web 应用程序上:

@Query("select t1 from CheckList as t1 join "
        + "(select pratica, documentazione, max(versione) as versione from CheckList group by pratica, documentazione) as t2 "
        + "on t1.pratica = t2.pratica  and "
        + "t1.documentazione = t2.documentazione and "
        + "t1.versione = t2.versione")

但它不起作用，因为我知道，要创建与 JPA 存储库的联接，您必须执行以下操作:

form table1 t1 join t1.table2 t2 on etc.

但是在这种情况下我需要在同一个表上进行联接，那么我该怎么做呢？

Answer 1

我解决了添加nativeQuery = true。

这是我的查询:

@Query(value = "select t1.* from macchine.check_list as t1 join "
        + "(select pratica, documentazione, max(versione) as versione from macchine.check_list group by pratica, documentazione) as t2 "
        + "on t1.pratica = t2.pratica  and "
        + "t1.documentazione = t2.documentazione and "
        + "t1.versione = t2.versione where t1.pratica = :pratica", nativeQuery = true)

需要明确的是，这是我的 table :

+----+----------------+---------+----------+---------+
| id | documentazione | pratica | versione | ....... |
+----+----------------+---------+----------+---------+
| 1  | 1              | 1       | 1        |         |
+----+----------------+---------+----------+---------+
| 2  | 2              | 1       | 1        |         |
+----+----------------+---------+----------+---------+
| 3  | 3              | 1       | 1        |         |
+----+----------------+---------+----------+---------+
| 4  | 1              | 1       | 2        |         |
+----+----------------+---------+----------+---------+

使用此查询的结果是这样的:

+----+----------------+---------+----------+---------+
| id | documentazione | pratica | versione | ....... |
+----+----------------+---------+----------+---------+
| 4  | 1              | 1       | 2        |         |
+----+----------------+---------+----------+---------+
| 2  | 2              | 1       | 1        |         |
+----+----------------+---------+----------+---------+
| 3  | 3              | 1       | 1        |         |
+----+----------------+---------+----------+---------+