如何修改SQL Limit逻辑获取5个交易记录并合并查询？

核心思路 #

先通过CTE（公共表达式）筛选出最新的N个交易ID（这里N=5），再基于这些ID关联查询交易详情和对应的商品信息，确保最终拿到的是N个完整交易，不受单个交易的商品数量影响。同时支持在Limit中使用参数，实现动态控制获取的交易数量。

优化后的SQL+Go代码 #

// 单次查询获取5个最新交易的完整信息（含关联商品）
rows, err := dh.QueryContext(ctx,
    `WITH top_transactions AS (
        SELECT transaction_id
        FROM transactions
        WHERE website_id = $1 AND username = $2
        ORDER BY created_at DESC
        LIMIT $3
    )
    SELECT 
        t.transaction_id, 
        t.status, 
        t.created_at, 
        t.expired_at, 
        pi.item_name
    FROM top_transactions tt
    JOIN transactions t ON tt.transaction_id = t.transaction_id AND t.website_id = $1 AND t.username = $2
    JOIN transaction_items ti ON t.website_id = ti.website_id AND t.transaction_id = ti.transaction_id
    JOIN product_items pi ON ti.website_id = pi.website_id AND pi.product_id = ti.product_id AND ti.product_item_id = pi.product_item_id
    ORDER BY t.created_at DESC`,
    websiteId, username, 5)
if err != nil {
    return nil, err
}
defer rows.Close()

// 定义交易结构体，用于组装结果
type Transaction struct {
    ID        string
    Status    string
    CreatedAt time.Time
    ExpiredAt time.Time
    Items     []string
}

// 用Map按交易ID分组，合并同一交易的多个商品
trxMap := make(map[string]*Transaction)
for rows.Next() {
    var trxId, status, itemName string
    var createdAt, expiredAt time.Time
    if err = rows.Scan(&trxId, &status, &createdAt, &expiredAt, &itemName); err != nil {
        return nil, err
    }

    if trx, exists := trxMap[trxId]; exists {
        trx.Items = append(trx.Items, itemName)
    } else {
        trxMap[trxId] = &Transaction{
            ID:        trxId,
            Status:    status,
            CreatedAt: createdAt,
            ExpiredAt: expiredAt,
            Items:     []string{itemName},
        }
    }
}

// 检查遍历结果时的错误
if err = rows.Err(); err != nil {
    return nil, err
}

// 将Map转为切片，并按创建时间倒序排列（保证顺序一致）
transactions := make([]*Transaction, 0, len(trxMap))
for _, trx := range trxMap {
    transactions = append(transactions, trx)
}
sort.Slice(transactions, func(i, j int) bool {
    return transactions[i].CreatedAt.After(transactions[j].CreatedAt)
})

return transactions, nil

关键说明 #

1. CTE的作用：top_transactions 子查询先锁定最新的5个交易ID，确保后续关联查询只针对这5个交易，不会因为商品多导致交易数量不足。
2. 参数化Limit：Limit部分使用$3作为参数，支持动态传入需要获取的交易数量，满足你的参数化需求。
3. 结果组装：通过Map分组交易ID，将同一交易的多个商品合并到Items切片中，最终得到结构清晰的交易信息列表。

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