求SQL中ROW_NUMBER开窗函数在Kotlin Exposed中的等效实现

你需要的SQL窗口函数ROW_NUMBER() OVER (PARTITION BY resto_id ORDER BY sold DESC) AS row_num，在Kotlin Exposed中可以通过内置的窗口函数API实现，以下是具体写法和完整查询示例：

1. 定义对应表实体 #

先确保已创建和数据库表匹配的Exposed表对象：

object Restaurants : Table("restaurant") {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", 255)
}

object MenuItems : Table("menu_item") {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", 255)
    val restoId = integer("resto_id").references(Restaurants.id)
    val sold = integer("sold")
}

2. 构造ROW_NUMBER窗口函数 #

直接对应原SQL中的窗口函数逻辑，Exposed写法如下：

// 构建ROW_NUMBER窗口函数并指定别名
val rowNum = RowNumber().over(
    window = Window.partitionBy(MenuItems.restoId) // 对应PARTITION BY resto_id
        .orderBy(MenuItems.sold, SortOrder.DESC) // 对应ORDER BY sold DESC
).alias("row_num") // 对应AS row_num

3. 完整查询实现 #

将窗口函数整合到查询中，还原原SQL的业务逻辑：

// 构建子查询，对应原SQL中的子查询部分
val menuSubQuery = MenuItems
    .slice(
        MenuItems.id,
        MenuItems.name,
        MenuItems.restoId,
        MenuItems.sold,
        rowNum
    )
    .selectAll()
    .alias("m")

// 主查询：关联餐厅表与子查询，筛选每家餐厅的Top3销量菜品
val top3ItemsPerRestaurant = Restaurants
    .join(menuSubQuery, JoinType.LEFT, Restaurants.id, menuSubQuery[MenuItems.restoId])
    .slice(
        Restaurants.id,
        Restaurants.name,
        menuSubQuery[MenuItems.id],
        menuSubQuery[MenuItems.name],
        menuSubQuery[MenuItems.sold]
    )
    .select { menuSubQuery[rowNum] <= 3 } // 对应WHERE m.row_num <=3
    .orderBy(Restaurants.id, menuSubQuery[rowNum]) // 对应ORDER BY r.id, m.row_num

// 遍历处理查询结果
top3ItemsPerRestaurant.forEach { resultRow ->
    val restaurantId = resultRow[Restaurants.id]
    val restaurantName = resultRow[Restaurants.name]
    val itemId = resultRow[menuSubQuery[MenuItems.id]]
    val itemName = resultRow[menuSubQuery[MenuItems.name]]
    val soldCount = resultRow[menuSubQuery[MenuItems.sold]]
    
    println("餐厅: $restaurantName($restaurantId) | 菜品: $itemName($itemId) | 销量: $soldCount")
}

关键对应关系说明 #

• RowNumber() → SQL中的ROW_NUMBER()函数
• Window.partitionBy(MenuItems.restoId) → PARTITION BY resto_id分区逻辑
• orderBy(MenuItems.sold, SortOrder.DESC) → ORDER BY sold DESC排序规则
• .alias("row_num") → AS row_num别名定义

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