php+mysql 输出table 如何横向排列

Question

数据库表 table

id	cid	riqi	rate
1	12	2018	1200
2	12	2019	1300
3	12	2020	1400
4	13	2018	2200
5	13	2019	2600
6	13	2020	1900

正常SELECT出来，然后php 遍历输出是竖排，如何才能向下面这样横排呢？

cid	2020	2019	2018
12	1400	1300	1200
13	1900	2600	2200

Answer 1

select * from table where x=x group by year
测试数据：

# 执行语句
SELECT
    IFNULL( cid, '没有cid' ) AS cid,
    SUM(
    IF
    ( riqi = '2018', rate, 0 )) AS `2018`,
    SUM(
    IF
    ( riqi = '2019', rate, 0 )) AS `2019`,
    SUM(
    IF
    ( riqi = '2020', rate, 0 )) AS `2020` 
FROM
    q 
GROUP BY
    riqi;

执行结果：

Answer 2

$originalRaws = [
    ['id' => 1, 'cid' => 12, 'riqi' => 2018, 'rate' => 1200],
    ['id' => 2, 'cid' => 12, 'riqi' => 2019, 'rate' => 1300],
    ['id' => 3, 'cid' => 12, 'riqi' => 2020, 'rate' => 1400],
    ['id' => 4, 'cid' => 13, 'riqi' => 2018, 'rate' => 2200],
    ['id' => 5, 'cid' => 13, 'riqi' => 2019, 'rate' => 2600],
    ['id' => 6, 'cid' => 13, 'riqi' => 2020, 'rate' => 1900],
];

$data  = [];
$dates = [];

foreach ($originalRaws as $originalRaw) {
    $dates[] = $originalRaw['riqi'];
    if (!isset($data[$originalRaw['cid']])) {
        $data[$originalRaw['cid']] = [$originalRaw['riqi'] => $originalRaw['rate']];
    } else {
        $data[$originalRaw['cid']][$originalRaw['riqi']] = $originalRaw['rate'];
    }
}
$dates = array_unique($dates);
rsort($dates);

echo 'id | ' . array_reduce($dates, function ($item, $result) {
        if ($item) {
            return $item . ' | ' . $result;
        } else {
            return $result;
        }
    });

foreach ($data as $cid => $datum) {
    $rates = [];
    foreach ($dates as $date) {
        $rates[] = $datum[$date];
    }
    echo "\n" . $cid . ' | ' . implode(' | ', $rates);
}

输出结果

id | 2020 | 2019 | 2018
12 | 1400 | 1300 | 1200
13 | 1900 | 2600 | 2200