HQL查询帮助（分组依据、排序依据）

Question

我在编写 HQL 来显示表中以下数据的最新应用程序（最新的 createDate）的不同 applicationId 时遇到问题。

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | Henry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-03 00:00:00 | 
|             5 | Cindy Spahn | Wilson   | 2010-05-04 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
5 rows in set (0.00 sec)

以下是我正在寻找的结果：

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | Henry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

实体如下：

@Entity
@Table(name = "application")
public class Application {
    private long applicationId;
    private String firstName;
    private String lastName;
    private List<ApplicationHistory> applicationHistoryList;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getApplicationId() {
        return applicationId;
    }

    @OneToMany(mappedBy = "application", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<ApplicationHistory> getApplicationHistoryList() {
        return applicationHistoryList;
    }
    // getter() and setter()
}

和：

@Entity
@Table(name = "applicationHistory")
public class ApplicationHistory {
    private Application application;
    private final Timestamp createDate = new Timestamp(System.currentTimeMillis());

    @ManyToOne
    @JoinColumn(name = "applicationId", insertable = false, updatable = false)
    public Application getApplication() {
        return application;
    }

    @Id
    @Column(columnDefinition = "timestamp default current_timestamp")
    public Timestamp getCreateDate() {
        return createDate;
    }
}

Answer 1

您可以使用以下查询：

select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) 

例如：

Query q = em.createQuery("select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) ");

        List list = q.getResultList();

        for (Iterator iterator = list.iterator(); iterator.hasNext();) {
            Object obj[] = (Object[])iterator.next();
            Application a =  (Application) obj[0];

            System.out.println("ApplicationId="+a.getApplicationId() );
            System.out.println("CreateDate="+obj[1] );

        }

Answer 2

尝试使用 group by 子句来做到这一点：

select ah from ApplicationHistory ah group by ah.applicationId order by ah.createDate desc