如何用HQL按staffId分组获取StaffDuty表最新记录？

问题背景 #

要从StaffDuty表中查询指定部门（dId）、且值班时间覆盖指定日期（selDate）的每位员工的最新值班记录，但原查询因为GROUP BY先于ORDER BY执行，无法拿到正确结果。原查询语句：

query = session.createQuery("FROM StaffDuty where deptId.id = :dId and DATE(startDate) <= DATE(:selDate) and DATE(endDate) >= DATE(:selDate) group by staffId.id order by createdOn desc");

对应的StaffDuty实体类：

@Entity
@Table(name = "staff_duty")
public class StaffDuty implements Serializable {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;

 @JoinColumn(name = "staff_id", referencedColumnName = "id", nullable = false)
 @OneToOne(optional = false)
 private Staff staffId;

 @JoinColumn(name = "dept_id", referencedColumnName = "id", nullable = false)
 @OneToOne(optional = false)
 private Section deptId;

 @Column(name = "start_date")
 @JsonAdapter(DateJsonSerializer.class)
 private Date startDate;

 @Column(name = "end_date")
 @JsonAdapter(DateJsonSerializer.class)
 private Date endDate;

 @Column(name = "duty")
 private String duty;

 @Column(name="created_on")
 private Timestamp createdOn;

 // 省略构造、getter/setter等方法
}

可行解决方案 #

方案1：子查询匹配最新创建时间 #

先通过子查询找到每个符合条件员工的最新createdOn时间，再关联查询完整记录：

String hql = "FROM StaffDuty sd " +
             "WHERE sd.deptId.id = :dId " +
             "AND DATE(sd.startDate) <= DATE(:selDate) " +
             "AND DATE(sd.endDate) >= DATE(:selDate) " +
             "AND sd.createdOn = (" +
                 "SELECT MAX(sd2.createdOn) " +
                 "FROM StaffDuty sd2 " +
                 "WHERE sd2.staffId.id = sd.staffId.id " +
                 "AND sd2.deptId.id = :dId " +
                 "AND DATE(sd2.startDate) <= DATE(:selDate) " +
                 "AND DATE(sd2.endDate) >= DATE(:selDate)" +
             ")";

Query query = session.createQuery(hql);
query.setParameter("dId", dId);
query.setParameter("selDate", selDate);
List<StaffDuty> latestDuties = query.list();

逻辑：先锁定符合部门和日期范围的记录，再给每个员工筛选出最新的createdOn，最后匹配对应记录，确保拿到每个员工的最新条目。

方案2：用窗口函数筛选（Hibernate 5.2+支持） #

如果你的Hibernate版本在5.2及以上，支持MySQL窗口函数，可以用ROW_NUMBER()给每个员工的记录排序，取第一条：

String hql = "SELECT sd FROM (" +
             "    SELECT sd, " +
             "           ROW_NUMBER() OVER (PARTITION BY sd.staffId.id ORDER BY sd.createdOn DESC, sd.id DESC) AS rn " +
             "    FROM StaffDuty sd " +
             "    WHERE sd.deptId.id = :dId " +
             "    AND DATE(sd.startDate) <= DATE(:selDate) " +
             "    AND DATE(sd.endDate) >= DATE(:selDate)" +
             ") t " +
             "WHERE t.rn = 1";

Query query = session.createQuery(hql);
query.setParameter("dId", dId);
query.setParameter("selDate", selDate);
List<StaffDuty> latestDuties = query.list();

逻辑：按员工ID分组（PARTITION BY），每组内按createdOn倒序（再按id倒序处理同时间多条记录的情况），每组编号为1的就是最新记录。

额外提示 #

• 如果存在同一员工同一createdOn时间有多条记录的场景，建议在排序时加上id字段（因为id是自增主键，更大的id对应更晚创建的记录），避免返回随机结果。
• 确保selDate参数是合法的日期类型，避免DATE()函数转换出错。

内容的提问来源于stack exchange，提问作者KJEjava48