SQL将列中的多个值连接到一个单元格中 [英] SQL join multiple values from column into one cell

问题描述

已经有大量的sql join q，但是我没有看到我的答案，所以去了. . .我正在使用WPDB(Wordpress数据库)/EZSql/MySQL 5.0.尝试实现下面的简单"所需输出并不容易.

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.

当前输出

MemberID          MemberName              FruitName
--------------    ---------------------   --------------
1                  Al                     Apple
1                  Al                     Cherry

所需的输出

MemberID           MemberName            FruitName
-----------        --------------        ------------
1                  Al                    Apple, Cherry

MemberID来自表a，MemberName来自表a和表b，FruitName来自表b.因为我要从表a中输出很多其他列，所以我通过此查询左联接"了两个表:

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:

$contents = $wpdb->get_results( $wpdb->prepare("SELECT * FROM a LEFT JOIN b ON a.MemberName = b.MemberName"));

我稍后使用echo打印列:

I later print the columns using 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 here and modeled my question after it but I don't understand their solutions and am hoping for something simpler.

推荐答案

GROUP BY MemberName和GROUP_CONCAT(FruitName).例如，

GROUP BY MemberName and GROUP_CONCAT(FruitName). For example,

SELECT MemberId, MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName;

这篇关于SQL将列中的多个值连接到一个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！