如何使用Kotlin Exposed SQL DSL实现分组查询各分组最新版本记录

你的问题核心是直接在GROUP BY查询中选择非聚合的主键字段违反了SQL规范，导致数据库抛出UserTable.id must appear in the GROUP BY clause or be used in an aggregate function错误。要获取每个group_id下最新version的完整记录，需要用子查询关联或窗口函数两种方式实现，下面是具体的Exposed代码：

方法一：子查询关联（兼容所有SQL数据库） #

先通过子查询找出每个分组的最大版本号，再关联原表匹配对应的完整记录：

1. 定义表结构（对应你的interaction表） #

object InteractionTable : Table("interaction") {
    val id = integer("interaction_id").autoIncrement()
    val groupId = integer("group_id")
    val version = integer("version")
    override val primaryKey = PrimaryKey(id)
}

2. 编写查询逻辑 #

// 子查询：获取每个group_id对应的最大version
val maxVersionSubquery = InteractionTable
    .slice(InteractionTable.groupId, InteractionTable.version.max())
    .selectAll()
    .groupBy(InteractionTable.groupId)
    .alias("max_version")

// 关联原表，筛选出每个分组中version等于最大值的记录
val latestInteractions = InteractionTable
    .join(
        maxVersionSubquery,
        JoinType.INNER,
        additionalConstraint = {
            InteractionTable.groupId eq maxVersionSubquery[InteractionTable.groupId] and
            (InteractionTable.version eq maxVersionSubquery[InteractionTable.version.max()])
        }
    )
    .slice(InteractionTable.id, InteractionTable.groupId, InteractionTable.version)
    .selectAll()
    .toList()

方法二：窗口函数（简洁高效，支持PostgreSQL、MySQL 8+等） #

使用row_number()窗口函数，按group_id分区、version降序排序，取每个分区的第一条记录：

// 定义窗口函数：按groupId分区，version降序排序，生成行号
val rowNum = WindowFunction.rowNumber().over(
    PartitionBy(InteractionTable.groupId),
    OrderBy(InteractionTable.version, SortOrder.DESC)
).alias("row_num")

// 筛选出行号为1的记录（即每个分组的最新版本）
val latestInteractions = InteractionTable
    .slice(InteractionTable.id, InteractionTable.groupId, InteractionTable.version, rowNum)
    .selectAll()
    .having(rowNum eq 1)
    .toList()

结果验证 #

针对你提供的示例数据，两种方法都会返回以下结果：

内容的提问来源于stack exchange，提问作者Francisco Durdin Garcia