Tp8对于多表统计销售额排行榜且做关联查询，避免n+1等性能问题

业务里很常见一个需求：

两张表都有 uid 和 total_money
需要统计两个表的金额总和
按总金额做排行榜
并关联 user 表拿用户信息

很多人第一反应直接 join，结果数据直接翻倍 🤦‍♂️
这篇文章把正确做法讲清楚。

---

❌ 错误写法（不要这样写）

SELECT a.uid,
       SUM(a.total_money + b.total_money)
FROM table_a a
JOIN table_b b ON a.uid = b.uid
GROUP BY a.uid;

假设：

• table_a 某用户 3 条记录
• table_b 某用户 2 条记录

JOIN 后会变成：3 × 2 = 6 条数据

✅ 正确思路

正确步骤：

1. table_a 先按 uid 分组
2. table_b 先按 uid 分组
3. UNION ALL 合并
4. 再次按 uid 汇总
5. 排序

SQL 如下：

SELECT t.uid,
       SUM(t.payment) AS total_payment
FROM (
    SELECT uid, SUM(total_money) AS payment
    FROM table_a
    GROUP BY uid

    UNION ALL

    SELECT uid, SUM(total_money) AS payment
    FROM table_b
    GROUP BY uid
) t
GROUP BY t.uid
ORDER BY total_payment DESC;

在TP8的构建器如何实现？

假设

• table_a(uid, total_money)
• table_b(uid, total_money)
• user(id, nickname, avatar, mobile)

第一步：A/B 分组

use think\facade\Db;

$subA = Db::name('table_a')
    ->field('uid, SUM(total_money) AS payment')
    ->group('uid');

$subB = Db::name('table_b')
    ->field('uid, SUM(total_money) AS payment')
    ->group('uid');

第二步：UNION ALL

$unionSql = $subA->unionAll($subB)->buildSql();

第三步：二次汇总

$rankSql = Db::table($unionSql . ' t')
    ->field('customer_id, SUM(payment) AS total_payment')
    ->group('customer_id')
    ->buildSql();

with 关联 user

如果不想创建临时的模型文件，可以使用匿名模型：

use think\Model;
use app\model\User;

$RankModel = new class extends Model {
    public function user()
    {
        return $this->belongsTo(User::class, 'uid', 'id');
    }
};

$list = $RankModel
    ->with(['user' => function($q){
        $q->field('id,nickname,avatar,mobile');
    }])
    ->table($rankSql . ' r')
    ->alias('r')
    ->field('r.uid, r.total_payment')
    ->order('r.total_payment', 'desc')
    ->paginate(20);

return $list;

返回数据结构

{
  "uid": 12,
  "total_payment": 9980,
  "user": {
    "id": 12,
    "nickname": "张三",
    "avatar": "xxx.jpg",
    "mobile": "138xxxx"
  }
}

⚡ 性能优化建议

添加索引

INDEX idx_customer_id (customer_id);

如果有时间范围：

INDEX idx_customer_time (customer_id, create_time);

大数据量建议缓存

排行榜属于读多写少场景，可以：

• 定时生成排行榜
• Redis 缓存前 100 名
• 接口直接读缓存