PHP基于共享主键（email）合并二维关联数组

现有两个来自不同数据源的PHP二维关联数组$CVS_results和$BC_results，需以email作为共享主键，合并为包含双方字段（CVS_ID、email、BC_ID）的二维数组，而非简单拼接数组。

示例数组 #

$CVS_results = [
    ["CVS_ID" => "FZ00000001081", "email" => "tpastern@stjudes.org"],
    ["CVS_ID" => "FZ00000001082", "email" => "jpastern@stjudes.org"],
    ["CVS_ID" => "FZ00000001083", "email" => "rpastern@stjudes.org"]
];

$BC_results = [
    ["BC_ID" => "QR00000001085", "email" => "tpastern@stjudes.org"],
    ["BC_ID" => "QR00000001086", "email" => "jpastern@stjudes.org"],
    ["BC_ID" => "QR00000001087", "email" => "rpastern@stjudes.org"]
];

期望合并结果 #

$merged = [
    ["CVS_ID" => "FZ00000001081", "email" => "tpastern@stjudes.org", "BC_ID" => "QR00000001085"],
    ["CVS_ID" => "FZ00000001082", "email" => "jpastern@stjudes.org", "BC_ID" => "QR00000001086"],
    ["CVS_ID" => "FZ00000001083", "email" => "rpastern@stjudes.org", "BC_ID" => "QR00000001087"]
];

现有尝试代码 #

UPDATE 1 #

$BC_results = BC_cust_query($qlist, true);
$CVS_results = CVS_query_GP_ID($BC_results);

$merged = array() ;
foreach ($BC_results as $row){
    foreach($CVS_results as $item){
        if (strcmp($row['email'], $item['email'])){
            $merged = array([$row['email'],$row['BC_ID'],$item['CVS_ID']]);
        }
    }
}

var_dump($merged);

UPDATE 2 #

$merged = array() ;
foreach ($BC_results as $row){
    foreach($CVS_results as $item){
        $ii +=1;
        if ((strcmp(strtoupper(trim($row['email'])), strtoupper(trim($item['email'])))) == 0){
            $merged = array_merge($merged, array([$row['email'],$row['BC_ID'],$item['CVS_ID']]));
            break;
        }
    }
}

高效简洁的解决方案 #

嵌套循环的时间复杂度为O(n*m)，当数组元素较多时效率低下。可以通过将其中一个数组转换为以email为键的映射数组，将查找操作的时间复杂度降到O(1)，整体时间复杂度优化为O(n+m)，代码也更简洁。

实现代码 #

// 1. 将CVS_results转换为email => CVS_ID的映射数组，统一处理email格式避免匹配问题
$cvsEmailMap = [];
foreach ($CVS_results as $cvsRow) {
    $normalizedEmail = strtoupper(trim($cvsRow['email']));
    $cvsEmailMap[$normalizedEmail] = $cvsRow['CVS_ID'];
}

// 2. 遍历BC_results，快速匹配合并数据
$merged = [];
foreach ($BC_results as $bcRow) {
    $normalizedEmail = strtoupper(trim($bcRow['email']));
    // 检查当前email是否在CVS映射中存在
    if (isset($cvsEmailMap[$normalizedEmail])) {
        $merged[] = [
            'CVS_ID' => $cvsEmailMap[$normalizedEmail],
            'email' => $bcRow['email'], // 保留原email格式，若需要统一格式可改用$normalizedEmail
            'BC_ID' => $bcRow['BC_ID']
        ];
    }
    // 可选：如果需要保留无匹配的项，添加else分支
    // else {
    //     $merged[] = [
    //         'CVS_ID' => null,
    //         'email' => $bcRow['email'],
    //         'BC_ID' => $bcRow['BC_ID']
    //     ];
    // }
}

var_dump($merged);

方案优势 #

• 效率更高：避免嵌套循环的重复遍历，处理大数据量时性能提升明显
• 代码更简洁：逻辑清晰，易维护
• 鲁棒性强：通过strtoupper(trim())统一处理email格式，避免大小写、首尾空格导致的匹配失败

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