面试题：Kotlin的Exposed框架中多表关联查询实现

Question

假设有两个表，一个是`Users`表包含`id`、`name`字段，另一个是`Orders`表包含`id`、`userId`（关联`Users`表的`id`）、`orderAmount`字段。使用Exposed框架，编写Kotlin代码实现查询每个用户及其对应的订单总金额。

Answer 1

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction

// 定义Users表
object Users : Table() {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", 50)
}

// 定义Orders表
object Orders : Table() {
    val id = integer("id").autoIncrement().primaryKey()
    val userId = reference("userId", Users.id)
    val orderAmount = decimal("orderAmount", 10, 2)
}

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
    transaction {
        SchemaUtils.create(Users, Orders)

        // 插入一些测试数据
        Users.insert {
            it[name] = "Alice"
        }
        Users.insert {
            it[name] = "Bob"
        }

        Orders.insert {
            it[userId] = 1
            it[orderAmount] = 100.00
        }
        Orders.insert {
            it[userId] = 1
            it[orderAmount] = 200.00
        }
        Orders.insert {
            it[userId] = 2
            it[orderAmount] = 150.00
        }

        // 查询每个用户及其对应的订单总金额
        val result = Users.leftJoin(Orders)
           .slice(Users.id, Users.name, Orders.orderAmount.sum())
           .groupBy(Users.id, Users.name)
           .selectAll()
           .map {
                val userId = it[Users.id]
                val name = it[Users.name]
                val totalOrderAmount = it[Orders.orderAmount.sum()]
                UserOrderSummary(userId, name, totalOrderAmount)
            }

        result.forEach {
            println("User ID: ${it.userId}, Name: ${it.name}, Total Order Amount: ${it.totalOrderAmount}")
        }
    }
}

data class UserOrderSummary(
    val userId: Int,
    val name: String,
    val totalOrderAmount: BigDecimal?
)