MySQL如何按status分组每组最多返回10条客户数据

你原有的SQL直接加LIMIT只会限制全局返回的总条数，无法实现每个status单独限制最多10条的需求，两种常用实现方案如下：

MySQL 原生实现 #

方案1：MySQL 8.0+ 窗口函数实现（推荐） #

使用ROW_NUMBER()窗口函数按status_id分组给客户排序，仅保留每组排序前10的记录即可：

WITH ranked_customers AS (
    SELECT 
        customers.*,
        statuses.*,
        ROW_NUMBER() OVER (PARTITION BY customers.status_id ORDER BY customers.id DESC) AS rn
    FROM customers
    JOIN statuses ON customers.status_id = statuses.id
    WHERE statuses.id IN (18, 19, 20)
)
SELECT * FROM ranked_customers WHERE rn <= 10;

提示：ORDER BY customers.id DESC可以替换为你需要的业务排序规则，比如按created_at DESC取每个状态下最新的10条客户，排序逻辑可自定义。

方案2：MySQL 5.x 低版本兼容实现 #

如果你使用的是不支持窗口函数的低版本MySQL，可以用关联子查询实现同等逻辑：

SELECT c.*, s.*
FROM customers c
JOIN statuses s ON c.status_id = s.id
WHERE s.id IN (18, 19, 20)
AND (
    SELECT COUNT(*) 
    FROM customers c2 
    WHERE c2.status_id = c.status_id 
    AND c2.id >= c.id -- 此处比较条件对应上方窗口函数的排序规则，保持逻辑一致
) <= 10
ORDER BY c.status_id, c.id DESC;

Laravel Eloquent 实现 #

首先确保你已经定义好模型关联，在Customer模型中添加status关联方法：

// app/Models/Customer.php
public function status()
{
    return $this->belongsTo(\App\Models\Status::class);
}

对应窗口函数的Eloquent写法（适配MySQL 8.0+） #

$rankedQuery = \App\Models\Customer::selectRaw('customers.*, statuses.*, ROW_NUMBER() OVER (PARTITION BY customers.status_id ORDER BY customers.id DESC) as rn')
    ->join('statuses', 'customers.status_id', '=', 'statuses.id')
    ->whereIn('statuses.id', [18, 19, 20]);

$customers = \DB::table(\DB::raw("({$rankedQuery->toSql()}) as ranked_customers"))
    ->mergeBindings($rankedQuery->getQuery())
    ->where('rn', '<=', 10)
    ->get();

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