我有这个实体:
@Entity
public class TDInvestment {
@Id
private String tdName;
@Id
private LocalDate readingDate;
//more attributes
}
知道我有一个由String tdName
和LocalDate readingDate
标识的唯一实体,我如何选择所有最新的实体(使用Hibernate HQL或本机SQL(?我知道我可以使用... order by readingDate desc;
将最新行放在顶部,但是如何仅选择每个唯一tdName
的最新行?
更新
我试过使用
select tdName, max(readingDate)
from table
group by tdName;
并且仅当表只有这 2 列时才有效。当它有更多非唯一列时,如何使其工作?
示例(日/月/年(:
tdName readingDate unitPrice other non unique Columns
abc 10/02/2018 214125.34 ...
def 25/01/2012 134.21312 ...
abc 21/11/2015 54983.23 ...
def 19/07/2011 0.2374 ...
abc 01/03/2002 83271.1 ...
ghi 11/10/1950 12 ...
我的查询应该返回:
tdName readingDate unitPrice other non unique Columns
abc 10/02/2018 214125.34 ...
def 25/01/2012 134.21312 ...
ghi 11/10/1950 12 ...
SELECT * FROM TABLE o
WHERE o.readingDate = (
SELECT MAX(e.readingDate) FROM TABLE e
WHERE e.tdName = o.tdName
);
实际上,我发现我的答案与另一个答案非常接近。返回所有字段的完整选择查询如下所示:
select
t1.name,
t1.max,
t2.due_date,
t2.fixed_rate,
t2.unit_price,
t2.reading_time
from (select
t2.name,
max(t2.reading_date)
from td t2
where
t2.due_date > now()
group by t2.name) t1,
td t2
where
t1.name = t2.name
and t1.max = t2.reading_date;