HQL查询的java帮助(分组依据、订单依据)
对于表中的以下数据,我在编写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;
}
}
# 1 楼答案
尝试使用group by子句:
# 2 楼答案
您可以使用以下查询:
例如: