MySQL SQL 将列中的多个值连接到一个单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1151819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL join multiple values from column into one cell
提问by Adam
There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy.
已经有大量的 sql join q，但我没有看到我的答案，所以这里是。. . 我正在使用 WPDB（Wordpress 数据库）/EZSql/MySQL 5.0。事实证明，尝试实现下面的“简单”期望输出并不容易。
Current output
电流输出
MemberID MemberName FruitName
-------------- --------------------- --------------
1 Al Apple
1 Al Cherry
Desired output
期望输出
MemberID MemberName FruitName
----------- -------------- ------------
1 Al Apple, Cherry
MemberID comes from table a, MemberName comes from table a and table b, and FruitName comes from table b. Because I am outputting a lot of other columns from table a, I have 'left joined' the two tables through this query:
MemberID 来自表a，MemberName 来自表a 和表b，FruitName 来自表b。因为我从表 a 中输出了很多其他列，所以我通过这个查询“左连接”了这两个表：
$contents = $wpdb->get_results( $wpdb->prepare("SELECT * FROM a LEFT JOIN b ON a.MemberName = b.MemberName"));
I later print the columns using echo:
我稍后使用 echo 打印列：
<td><?php echo $content->MemberID ?></td>
<td><?php echo $content->MemberName ?></td>
<td><?php echo $content->FruitName ?></td>
I assume I should try to query/join the two tables in a different manner though it may be possible to get creative in printing the columns. I found this discussion hereand modeled my question after it but I don't understand their solutions and am hoping for something simpler.
我假设我应该尝试以不同的方式查询/连接两个表，尽管在打印列时可能会有创意。我在这里找到了这个讨论，并在此之后模拟了我的问题，但我不明白他们的解决方案，我希望有更简单的方法。
回答by
GROUP BY MemberName and GROUP_CONCAT(FruitName). For example,
GROUP BY MemberName 和 GROUP_CONCAT(FruitName)。例如，
SELECT MemberId, MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName;
回答by Lazy Bob
Given that you're using mySQL, I believe the group_concat function will do exactly what you're looking for:
鉴于您使用的是 mySQL，我相信 group_concat 函数将完全满足您的要求：
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
回答by Peter Eisentraut
SELECT MemberID, MemberName, GROUP_CONCAT(FruitName SEPARATOR ',') FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY MemberID, MemberName;
回答by Brimstedt
One way is to loop and build the output in PHP (or whatever language yo are using).
一种方法是在 PHP（或您使用的任何语言）中循环和构建输出。
Either concatenate the fruits when fetching the rows from db, or when presenting it to the user. Id suggest the latter, since it's more of a view-logic issue than anything else.
从数据库中获取行或将其呈现给用户时连接水果。我建议后者，因为它更像是一个视图逻辑问题。
If you want to do it in SQL you'll either have to create your own (I do not belive MySQL has it built in, but I might be wrong) aggregate function to concatenate strings. It is not so trivial.
如果你想在 SQL 中做到这一点，你要么必须创建自己的（我不相信 MySQL 内置了它，但我可能错了）聚合函数来连接字符串。这不是那么简单。
If you are fetching only one member, you can also do it in SQL by using a variable, Im uncertaing about the exact MySQL syntax, but for MSSQL it would be something like:
如果您只获取一个成员，您也可以通过使用变量在 SQL 中执行此操作，我不确定 MySQL 的确切语法，但对于 MSSQL，它将类似于：
DECLARE @frutis VARCHAR(MAX)
SELECT @fruits = ISNULL(@fruits + ', ' + FruitName, FruitName)
FROM ...
SELECT @fruits
回答by Lukin4Tips
@Adam I know it might be late to answer but I just ran into the same issue, so here's my solution to extend Mike answer and solve the multiple same fruit occurences:
@Adam 我知道回答可能晚了，但我遇到了同样的问题，所以这是我扩展 Mike 回答并解决多个相同水果出现的解决方案：
SELECT MemberId, MemberName, GROUP_CONCAT(distinct FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName;
So basically you just need to add "distinct" in the GROUP_CONCAT function.
所以基本上你只需要在 GROUP_CONCAT 函数中添加“distinct”。
I hope that helps.
我希望这有帮助。
