group_concat的用法
2020-06-08 本文已影响0人
lalalasa
目标：一列(user_id)相同，另一类(user_name)不相同，希望根据user_id拼接user_name
步骤：
原始数据：
+---------+-----------------------------+
| user_id | user_name | principal |
+---------+-----------------------------+
| 1 | arina.kan | 3M |
| 2 | thong | L'Oreal |
| 3 | suboko | xiaomi |
| 3 | suboko | huawei |
| 3 | suboko | 3M |
| 4 | alisya | CPD
| 5 | peggy.zhu | PDD
| 6 | woof | swisse
| 7 | liuwen | 3M
| 8 | caocao | HP
| 9 | kate | beiersdorf
| 10 | tanglin | aveeno
+---------+-----------------------------+
使用group_concat
mysql> select user_id,GROUP_CONCAT(user_name) from sp_user_principal_tab GROUP BY user_id limit 10;
+---------+-----------------------------+
| user_id | GROUP_CONCAT(user_name) |
+---------+-----------------------------+
| 1 | arina.kan |
| 2 | thong |
| 3 | suboko,suboko,suboko |
| 4 | alisya |
| 5 | peggy.zhu |
| 6 | woof |
| 7 | liuwen |
| 8 | caocao |
| 9 | kate |
| 10 | tanglin |
+---------+-----------------------------+
10 rows in set (0.01 sec)
