Laravel Query Builder如何按品牌统计拥有最多车辆的用户及数量？

1. 单条SQL肯定能实现！ #

完全可以用单条SQL搞定这个需求，核心思路是先统计每个用户对应每个品牌的车辆数，再用窗口函数给每个品牌下的用户按车辆数排名，最后挑出排名第一的用户。

直接能用的SQL代码 #

WITH brand_user_stats AS (
    SELECT 
        c.brand,
        u.username,
        COUNT(c.id) AS total_cars,
        -- 给每个品牌下的用户按车辆数降序排名，数量相同的会并列第一
        RANK() OVER (PARTITION BY c.brand ORDER BY COUNT(c.id) DESC) AS rank_num
    FROM cars c
    JOIN users u ON c.user_id = u.id
    GROUP BY c.brand, u.id, u.username
)
SELECT brand, username, total_cars
FROM brand_user_stats
WHERE rank_num = 1;

小细节说明 #

• 用CTE（brand_user_stats）先把基础统计结果拎出来，可读性更强
• 如果你的需求是只取一个并列第一的用户（不管哪个），把RANK()换成ROW_NUMBER()就行；要是想保留所有并列第一的，就用RANK()
• 分组的时候要带上u.id，避免数据库开启ONLY_FULL_GROUP_BY模式时报错（毕竟可能存在重名用户）

2. Laravel Query Builder的最优写法 #

对应上面的SQL，我们可以用Laravel的Query Builder来写，既符合框架风格，又能保证性能：

方案一：用CTE（Laravel 5.5及以上支持，推荐） #

$results = DB::withExpression('brand_user_stats', function ($query) {
    $query->select(
        'c.brand',
        'u.username',
        DB::raw('COUNT(c.id) AS total_cars'),
        DB::raw('RANK() OVER (PARTITION BY c.brand ORDER BY COUNT(c.id) DESC) AS rank_num')
    )
    ->from('cars as c')
    ->join('users as u', 'c.user_id', '=', 'u.id')
    ->groupBy('c.brand', 'u.id', 'u.username');
})
->select('brand', 'username', 'total_cars')
->from('brand_user_stats')
->where('rank_num', 1)
->get();

方案二：子查询兼容低版本 #

如果你的Laravel版本比较老，不支持CTE，用子查询也能实现：

// 先构建统计子查询
$subQuery = DB::select(
    'c.brand',
    'u.username',
    DB::raw('COUNT(c.id) AS total_cars'),
    DB::raw('RANK() OVER (PARTITION BY c.brand ORDER BY COUNT(c.id) DESC) AS rank_num')
)
->from('cars as c')
->join('users as u', 'c.user_id', '=', 'u.id')
->groupBy('c.brand', 'u.id', 'u.username');

// 基于子查询筛选结果
$results = DB::table(DB::raw("({$subQuery->toSql()}) as brand_user_stats"))
->mergeBindings($subQuery) // 别忘了绑定参数，避免SQL注入
->select('brand', 'username', 'total_cars')
->where('rank_num', 1)
->get();

针对你现有代码的补充 #

你之前写的DB::table('cars')->groupBy('brand');只是完成了最基础的分组，我们需要在此基础上关联用户表、统计车辆数、添加排名逻辑，上面的方案已经把这些步骤都整合进去了，直接用就行~

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