Spring JPA+PostgreSQL：如何查询单值ID并按createdAt排序？

实体类定义如下：

@Entity
class User {
    @Id
    val id: UUID
    val name: String
    val createdAt: OffsetDateTime
}

需求是创建查询，返回数据库中所有用户的id，并按createdAt字段排序。

最初尝试的代码：

interface UserRepository : JpaRepository<User, UUID> {
    @Query("SELECT DISTINCT u.id FROM User u ORDER BY u.createdAt")
    fun getIds(): List<UUID>
}

触发错误：SELECT DISTINCT, ORDER BY expressions must appear in select list

随后修改查询，将createdAt加入SELECT子句：

interface UserRepository : JpaRepository<User, Long> {
    @Query("SELECT DISTINCT u.id, u.createdAt FROM User u ORDER BY u.createdAt")
    fun getIds(): List<Long>
}

又出现错误：Could not convert '[Ljava.lang.Object;' to 'java.util.UUID' using 'org.hibernate.type.descriptor.java.UUIDJavaType' to wrap，原因是试图把多字段的查询结果直接转换为单个UUID类型。

需要解决：如何仅获取id列表，同时保持按createdAt排序的逻辑？

方法1：移除多余的DISTINCT（首选） #

因为id是实体的主键，本身具备唯一性，DISTINCT完全是冗余的，直接去掉即可解决排序字段不在SELECT列表的问题：

interface UserRepository : JpaRepository<User, UUID> {
    @Query("SELECT u.id FROM User u ORDER BY u.createdAt")
    fun getIds(): List<UUID>
}

也可以用Spring Data JPA的方法命名查询，无需手写SQL，更简洁：

interface UserRepository : JpaRepository<User, UUID> {
    // 升序排序
    fun findAllByOrderByCreatedAtAsc(): List<UUID>
    // 降序排序
    fun findAllByOrderByCreatedAtDesc(): List<UUID>
}

方法2：特殊场景下保留DISTINCT的处理 #

如果业务上确实需要对id去重（尽管主键不会重复，但假设存在特殊业务逻辑），可以通过子查询先排序再去重：

interface UserRepository : JpaRepository<User, UUID> {
    @Query("SELECT u.id FROM (SELECT u.id, u.createdAt FROM User u ORDER BY u.createdAt) u")
    fun getIds(): List<UUID>
}

或者使用窗口函数（需数据库支持，如PostgreSQL、MySQL 8+等）：

interface UserRepository : JpaRepository<User, UUID> {
    @Query("SELECT DISTINCT u.id FROM User u ORDER BY MIN(u.createdAt) OVER (PARTITION BY u.id)")
    fun getIds(): List<UUID>
}

方法3：通过投影类中转后提取id #

定义一个用于接收查询结果的投影类：

data class UserIdWithCreatedAt(val id: UUID, val createdAt: OffsetDateTime)

修改仓库方法返回该投影类的列表：

interface UserRepository : JpaRepository<User, UUID> {
    @Query("SELECT DISTINCT u.id as id, u.createdAt as createdAt FROM User u ORDER BY u.createdAt")
    fun getIdsWithCreatedAt(): List<UserIdWithCreatedAt>
}

最后在业务代码中提取id：

val userIds = userRepository.getIdsWithCreatedAt().map { it.id }

内容的提问来源于stack exchange，提问作者Gustavo Cesário