PHP实现按company_id分组生成用户位置序列编号的方法

数据老陈

2026-8-21

按company_id分组生成序列编号的解决方案

原代码问题分析

你当前的代码逻辑错误在于用唯一的id字段作为分组判断依据，每条记录的id都是不同的，导致每次循环都会重置seq为1，无法实现同一company_id内的序列递增。正确的判断依据应该是company_id。

解决方案一：MySQL查询直接生成序号（推荐）

通过MySQL用户变量在查询阶段直接生成分组内的序列编号，效率更高且逻辑更简洁：

SELECT 
    user_id, 
    company_id,
    @seq := CASE WHEN @current_company = company_id THEN @seq + 1 ELSE 1 END AS position,
    @current_company := company_id
FROM 
    user_company,
    (SELECT @current_company := NULL, @seq := 0) AS init
ORDER BY 
    company_id, id;

对应的PHP调用代码：

$sql = 'SELECT 
            user_id, 
            company_id,
            @seq := CASE WHEN @current_company = company_id THEN @seq + 1 ELSE 1 END AS position,
            @current_company := company_id
        FROM 
            user_company,
            (SELECT @current_company := NULL, @seq := 0) AS init
        ORDER BY 
            company_id, id';
$result = $conn->query($sql);

if($result->num_rows){
    while($obj = $result->fetch_object()){
        echo 'Company: '.$obj->company_id.', User: '.$obj->user_id.', position: '.$obj->position.'<br/>';
    }
}

解决方案二：修正PHP代码逻辑

如果希望在PHP层面处理序列，只需将判断依据改为company_id，并维护当前公司的状态：

$sql = 'SELECT `id`, `user_id`, `company_id` FROM `user_company` ORDER BY `company_id`, id';
$result = $conn->query($sql);

if($result->num_rows){
    $current_company = '';
    $seq = 1;
    while($obj = $result->fetch_object()){
        if($current_company != $obj->company_id){
            $seq = 1;
            $current_company = $obj->company_id;
        }else{
            $seq++;
        }
        
        echo 'Company: '.$obj->company_id.', User: '.$obj->user_id.', position: '.$seq.'<br/>';
    }
}