MySQL多行结果合并为一行group_concat()

在做一个项目的用户列表的时候,需要将一个用户的多辆车放在一行显示,但是普通查询出来的结果是:

Array
        (
            [0] => Array
                (
                    [id] => 29
                    [user_id] => 1
                    [car_no] => 234567
                )
 
            [1] => Array
                (
                    [id] => 21
                    [user_id] => 1
                    [car_no] => 23565
                )
 
            [2] => Array
                (
                    [id] => 23
                    [user_id] => 1
                    [car_no] => 12345
                )
 
            [3] => Array
                (
                    [id] => 28
                    [user_id] => 1
                    [car_no] => 124455
                )
 
            [4] => Array
                (
                    [id] => 6
                    [user_id] => 1
                    [car_no] => 12345
                )
 
            [5] => Array
                (
                    [id] => 7
                    [user_id] => 2
                    [car_no] => 22
                )
 
            [6] => Array
                (
                    [id] => 3
                    [user_id] => 2
                    [car_no] =>T863
                )
 
            [7] => Array
                (
                    [id] => 25
                    [user_id] => 3
                    [car_no] => 12345
                )
                );

但是我们想要的结果是:

Array(
    [0] => Array
        (
            [user_id] => 8
            [login] => 18620982882
            [car_no] => Array
                (
                    [0] =>A123
                )
 
        )
 
    [1] => Array
        (
            [user_id] => 7
            [login] => 
            [car_no] => Array
                (
                    [0] =>T456
                    [1] => 123456
                )
 
        )
 
    [2] => Array
        (
            [user_id] => 2
            [login] => 13318029123
            [car_no] => Array
                (
                    [0] =>T863
                    [1] => 22
                )
 
        )
 
    [3] => Array
        (
            [user_id] => 1
            [login] => 15088138542
            [car_no] => Array
                (
                    [0] => 12345
                    [1] => 23565
                    [2] => 12345
                    [3] => 124455
                    [4] => 234567
                )
 
        )
        )

我之前的方法是:

private function group_sort($unique, $num, $data) {
    foreach ($unique as $key => $value) {
        for ($i = 0; $i < $num; $i++) {
            if ($data[$i]['user_id'] == $value) {
                $data_format[$key]['user_id'] = $data[$i]['user_id'];
                $data_format[$key]['login'] = $data[$i]['login'];
                $data_format[$key]['car_no'][] = $data[$i]['car_no'];
            }
        }
    }
    return $data_format;
}

一个一个遍历,进行重组聚合,也可以到到效果,但是效率低下,代码也比较复杂,今天查阅资料发现一个简单的方法,mysql本身有这个功能,可以把多个结果整合为一行,默认用逗号隔开,sql语句如下:

select user_id,group_concat(car_no) as car_no from mf_car_info group by user_id;

产生的结果:

Array
(
    [0] => Array
        (

            [user_id] => 1
            [car_no] => 12345,23565,12345,124455,234567
        )
     [1] => Array
        (

            [user_id] => 2
            [car_no] =>T863,22
        )
 
    [2] => Array
        (

            [user_id] => 3
            [car_no] => sdfa,12345
        )

这个就简单多了,直接遍历分割一下即可:

foreach ($data as $key => $value) {
    $data[$key]['car_no'] = explode(',', $value['car_no']);
}
echo "<pre>";
print_r(json_encode($data));
echo "</pre>";

完整代码如下:

<?php
$dsn = 'mysql:dbname=xxxx;host=192.xxx.1.xxx;port=3306';
$username = 'ysp';
$password = '123456';
try {
	$pdo = new PDO($dsn, $username, $password);
	$pdo->exec('set names utf8');
	$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
	die('Could not connect to the database: ' . $e);
}

$statement = "select id,user_id,group_concat(car_no) as car_no from mf_car_info group by user_id";
$rows = $pdo->query($statement);

//获取总行数
// print_r($rows->rowCount());

//获取所有结果
$data = $rows->FetchAll();

foreach ($data as $key => $value) {
	$data[$key]['car_no'] = explode(',', $value['car_no']);
}

echo "<pre>";
print_r(json_encode($data));
echo "</pre>";
exit;

把不同品牌下面的所有应用按照品牌汇集输出:

SELECT brand, GROUP_CONCAT(DISTINCT(registed_app)) FROM `users` GROUP by brand;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

SHUIPING_YANG

你的鼓励是我创作的最大动力。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值