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

阿里 阅读:228 2021-09-06 11:58:12 评论:0
本文章主要介绍了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; 

标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
KIKK导航

KIKK导航

排行榜
关注我们

一个IT知识分享的公众号