php - 我怎样才能重新格式化这个数组？

Question

所以我有这个数据库表

从那张桌子上我想实现这个（我的预期结果）

我不知道处理这个问题的最佳方法，所以现在我创建了两个查询。

我的第一个查询

SELECT * FROM ( select convert(created_date,date) created_date,code,'' code2,count(code) total, 0 total2
            from morning_briefing
            where  convert(created_date,date) =  convert('" . $selected_date . "',date) and code IS NOT NULL
            group by code order by created_date desc) a order by total desc

我的第二个查询

SELECT * FROM ( select convert(created_date,date) created_date,''code,code2,0 total,count(code2) total2
            from morning_briefing
            where  convert(created_date,date) =  convert('" . $selected_date . "',date)
            and code2 IS NOT NULL
            group by code2 order by created_date desc) a order by total2 desc

以下是我的查询结果

Array
(
    [0] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => AA
            [code2] => 
            [total] => 2
            [total2] => 0
        )

    [1] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => AB
            [code2] => 
            [total] => 1
            [total2] => 0
        )

    [2] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => BB
            [code2] => 
            [total] => 1
            [total2] => 0
        )

)

那么我的第二个查询结果是这样的

Array
(
    [0] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => 
            [code2] => AB
            [total] => 0
            [total2] => 2
        )

    [1] => stdClass Object
        (
            [created_date] => 2021-03-16
            [code] => 
            [code2] => AA
            [total] => 0
            [total2] => 1
        )

)

所以，我想合并两个数组，然后我想订购total columndesc，第二个顺序是total2

顺便说一句，我正在使用 mysql 表。那么我怎样才能达到我的预期结果（上图），在此先感谢。

标签： phparrays

Answer 1

您可以遍历您的$results1and$result2并创建一个新数组，其中包含您预期结果格式的数据。

$total = max(count($result1), count($result2));
$formatted = [];

for($i = 0; $i < $total; $i++) {

    $formatted[] = [
        'code' => isset($result1[$i]) ? $result1[$i]->code : '-',
        'total' => isset($result1[$i]) ? $result1[$i]->total : 0,
        'code2' => isset($result2[$i]) ? $result2[$i]->code2: '-',
        'total2' => isset($result2[$i]) ? $result2[$i]->total2 : 0
    ];
}