如何将同ordernumber对应的articleID合并到同一数组中？

核心需求 #

将同一ordernumber对应的所有articleID合并到同一数组中，把原有的多订单行结构转换为按订单号分组、商品ID为数组的结构。

原始数据 #

查询结果表格 #

原始PHP数组定义 #

$orderTable = [
    0 => ["ordernumber" => 1111, "articleID" => 50, "customergroupID" => 1],
    1 => ["ordernumber" => 2222, "articleID" => 61, "customergroupID" => 2],
    2 => ["ordernumber" => 2222, "articleID" => 62, "customergroupID" => 2],
    3 => ["ordernumber" => 3333, "articleID" => 50, "customergroupID" => 1]
];

期望结果 #

array(3) {
    [0]=> array(3) {
        ["ordernumber"]=> int(1111)
        ["articleID"]=> array(1){
            [0]=> int(50)
        }
        ["customergroupID"]=> int(1)
    }
    [1]=> array(3) {
        ["ordernumber"]=> int(2222)
        ["articleID"]=> array(2) {
            [0]=> int(61)
            [1]=> int(62)
        }
        ["customergroupID"]=> int(2)
    }
    [2]=> array(3) {
        ["ordernumber"]=> int(3333)
        ["articleID"]=> array(1){
            [0]=> int(50)
        }
        ["customergroupID"]=> int(1)
    }
}

方案一：PHP数组遍历处理 #

通过遍历原始数组，以ordernumber为键进行分组，将对应articleID收集到数组中，最后重新索引得到目标结构：

$mergedOrders = [];

foreach ($orderTable as $item) {
    $orderNumber = $item['ordernumber'];
    // 检查该订单号是否已存在分组
    if (!isset($mergedOrders[$orderNumber])) {
        // 初始化分组，将articleID转为数组
        $mergedOrders[$orderNumber] = [
            'ordernumber' => $orderNumber,
            'articleID' => [$item['articleID']],
            'customergroupID' => $item['customergroupID']
        ];
    } else {
        // 追加商品ID到已有分组的数组中
        $mergedOrders[$orderNumber]['articleID'][] = $item['articleID'];
    }
}

// 重新索引数组，去掉订单号作为键的结构
$mergedOrders = array_values($mergedOrders);

// 输出结果
print_r($mergedOrders);

方案二：SQL查询直接聚合（更高效） #

如果可以修改SQL语句，直接在查询阶段完成分组聚合，减少PHP端的处理成本：

SELECT 
    so.`ordernumber`,
    GROUP_CONCAT(sod.`articleID` SEPARATOR ',') AS articleIDs,
    scc.`id` AS `customergroupID`
FROM `s_order` so
INNER JOIN `s_order_details` sod ON so.`id` = sod.`orderID`
INNER JOIN `s_order_attributes` soa ON so.`id` = soa.`orderID`
INNER JOIN `s_user` su ON so.userID = su.`id`
INNER JOIN `s_core_customergroups` scc ON su.`customergroup` = scc.`groupkey`
WHERE soa.`twp_insert_article` = 0 
  AND so.ordernumber != 0
  AND so.ordertime > DATE_SUB(NOW(), INTERVAL 21 day)
GROUP BY so.`ordernumber`, scc.`id`;

拿到查询结果后，只需将articleIDs字符串转为数组即可：

// 假设从数据库获取的结果集为$dbResult
$finalOrders = [];
foreach ($dbResult as $row) {
    $finalOrders[] = [
        'ordernumber' => $row['ordernumber'],
        'articleID' => explode(',', $row['articleIDs']),
        'customergroupID' => $row['customergroupID']
    ];
}

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