java - 如何使用 JPQL 连接同一个表

Question

我的数据库中有一个表，其中包含体育结果，我需要从表中选择特定赛事阶段的参赛者的最后结果。

我有这张表:

+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| EventStageID   | int(11)       | NO   | PRI | NULL    |       |
| CompetitorID   | int(11)       | NO   | PRI | NULL    |       |
| Lap            | int(11)       | NO   | PRI | NULL    |       |
| Time           | varchar(255)  | YES  |     | NULL    |       |
| Status         | varchar(255)  | YES  |     | NULL    |       |
| PitstopCount   | int(11)       | YES  |     | NULL    |       |
| Grid           | int(11)       | YES  |     | NULL    |       |
| FastestLapTime | varchar(255)  | YES  |     | NULL    |       |
| Substatus      | varchar(255)  | YES  |     | NULL    |       |
| Points         | decimal(10,2) | YES  |     | NULL    |       |
| Position       | int(11)       | YES  |     | NULL    |       |
| StageType      | int(11)       | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+

我可以使用普通的 SQL 查询从表中进行选择，如下所示:

SELECT * FROM 
  (SELECT EventStageID as esi, CompetitorID as cid, Max(Lap) as MaxLap FROM srt_outright_season_event_stage_result_live WHERE EventStageID = 191666 GROUP BY CompetitorID) as y 
LEFT JOIN 
  (SELECT * FROM srt_outright_season_event_stage_result_live) as x 
ON x.CompetitorID = y.cid AND x.Lap = y.MaxLap AND x.EventStageID = y.esi;

这给出了以下结果:

+--------+--------+--------+--------------+--------------+------+----------+--------+--------------+------+----------------+-----------+--------+----------+-----------+
| esi    | cid    | MaxLap | EventStageID | CompetitorID | Lap  | Time     | Status | PitstopCount | Grid | FastestLapTime | Substatus | Points | Position | StageType |
+--------+--------+--------+--------------+--------------+------+----------+--------+--------------+------+----------------+-----------+--------+----------+-----------+
| 191666 |   4521 |      0 |       191666 |         4521 |    0 | Finished | NULL   |         NULL | NULL | 2:00.175       | NULL      |   NULL |        4 |         5 |
| 191666 |   4524 |      0 |       191666 |         4524 |    0 | Finished | NULL   |         NULL | NULL | 2:04.053       | NULL      |   NULL |       10 |         5 |
| 191666 |   4533 |      0 |       191666 |         4533 |    0 | Finished | NULL   |         NULL | NULL | NULL           | NULL      |   NULL |       13 |         5 |
| 191666 |   4538 |      0 |       191666 |         4538 |    0 | Finished | NULL   |         NULL | NULL | 2:01.218       | NULL      |   NULL |        6 |         5 |
| 191666 |   5769 |      0 |       191666 |         5769 |    0 | Finished | NULL   |         NULL | NULL | 2:00.050       | NULL      |   NULL |        3 |         5 |
| 191666 |   7135 |      0 |       191666 |         7135 |    0 | Finished | NULL   |         NULL | NULL | 1:59.431       | NULL      |   NULL |        1 |         5 |
| 191666 |   7138 |      0 |       191666 |         7138 |    0 | Finished | NULL   |         NULL | NULL | NULL           | NULL      |   NULL |       18 |         5 |
| 191666 |   7610 |      0 |       191666 |         7610 |    0 | Finished | NULL   |         NULL | NULL | 1:59.486       | NULL      |   NULL |        2 |         5 |
+--------+--------+--------+--------------+--------------+------+----------+--------+--------------+------+----------------+-----------+--------+----------+-----------+

我有这个实体类:

@Entity(name = "event_stage_result_live")
public class EventStageResultLive {

    @EmbeddedId
    private PKEventStageResultLive pkEventStageResultLive;
    // Composite PK contains EventStageID, CompetitorID and Lap

    @Column(name = "Time")
    private String time;

    @Column(name = "Status")
    private String status;

    @Column(name = "PitstopCount")
    private Integer pitstopCount;

    @Column(name = "Grid")
    private Integer grid;

    @Column(name = "Position")
    private Integer position;

    @Column(name = "FastestLapTime")
    private String fastestLapTime;

    @Column(name = "Substatus")
    private String substatus;

    @Column(name = "Points")
    private Float points;

    @Column(name = "StageType")
    private StageType stageType;

    // getters and setters...
}

Answer 1

我认为在 SQL 中你可以做这样的事情。我认为不需要加入。

select * from srt_outright_season_event_stage_result_live c
    where c.CompetitorID = :competitorID and c.EventStageID = 191666 
        and c.Lap =  (select max(d.lap) from  srt_outright_season_event_stage_result_live d
                where d.CompetitorID = :competitorID and d.EventStageID = 191666 )

传递给 JPQL 的是

select e from EventStageResultLive e
    where e.pkEventStageResultLive.CompetitorID = :competitorID and c.pkEventStageResultLive.EventStageID = 191666 
        and e.pkEventStageResultLive.Lap =  (select max(d.pkEventStageResultLive.lap) from  EventStageResultLive d
                where d.pkEventStageResultLive.CompetitorID = :competitorID and d.pkEventStageResultLive.EventStageID = 191666 )

假设

public class PKEventStageResultLive{
  private int CompetitorID ;
  private int EventStageID ;
  private int Lap;
}

如果属性名称不同，请更正 JPQL 中的名称

竞争对手 ID 作为命名参数。