mysql在一列中加入多个值 [英] mysql join with multiple values in one column
问题描述
我需要进行一个查询，该查询创建来自2个具有以下关系的表的3列:
I need to make a query that creates 3 columns that come from 2 tables which have the following relations:
表1的列ID与具有列ID2的表2相关联
TABLE 1 has Column ID that relates to TABLE 2 with column ID2
在表1中，有一列称为用户在表2中，有一列称为名称
In TABLE 1 there is a column called user In TABLE 2 there is a column called names
可以有1个唯一用户，但可以有许多与该用户相关联的名称.
There can be 1 unique user but there can be many names associated to that user.
如果执行以下操作，我将获取所有数据，但user列将为其关联的每个名称重复其自身.我想要的是用来显示唯一性，但名称列与所有与用户列相关联的名称一起出现，但用逗号分隔，如下所示:
If i do the following i get all data BUT the user column repeats itself for each name it has associated. What i want is for use to appear unique but the names columns appear with all the names associated to the user column but separated by commas, like the following:
选择用户，来自TABLE1的名称与TABLE1.id = TABLE2.id上的TABLE2左连接
select user,names from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id
这将显示用户每次出现该用户的名称时都会重复.我想要的是这样显示:
This will show the users repeated everytime a name appears for that user. what i want is to appear like this:
USER-NAMES
赛勒斯-佩德罗克斯，兰博，塞尔达
男生-卡门，卡洛斯，汤姆，桑德拉
杰里-克里斯汀·塞恩费尔德
忍者-soloboy
USER - NAMES
cyrex - pedrox, rambo, zelda
homeboy - carmen, carlos, tom, sandra
jerry - seinfeld, christine
ninja - soloboy
等...
推荐答案
What you are looking for is the GROUP_CONCAT operator.
select user, GROUP_CONCAT(names SEPARATOR ',')
from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id
group by user
这篇关于mysql在一列中加入多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
