SQL 将列中的多个值连接到一个单元格中

发布于 2024-07-29 07:39:17 字数 1615 浏览 5 评论 0原文

已经有大量的 SQL JOIN 问题,但我没有看到我的答案,所以就在这里。

我正在使用辅助类 wpdbezsql。 事实证明,尝试实现下面的“简单”所需输出并不容易。

当前输出:

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

所需输出:

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

MemberID 来自表 aMemberName 来自表 abFruitName 来自表 b。 因为我从表 a 中输出许多其他列,所以我通过此查询“左连接”了两个表:

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

我稍后使用 echo 打印这些列:

        <td><?php echo $content->MemberID ?></td>
        <td><?php echo $content->MemberName ?></td>
        <td><?php echo $content->FruitName ?></td>

我认为我应该尝试查询/尽管可以在打印列时发挥创意,但以不同的方式连接两个表。 我发现了这个这里的讨论并以此为基础模拟了我的问题,但是我不明白他们的解决方案,希望有更简单的解决方案。

There are a ton of SQL JOIN questions already, but I didn't see my answer so here it goes.

I am working with MySQL 5.0 and Wordpress Database using helper classes wpdb and ezsql. Trying to achieve the 'simple' desired output below has not proven to be easy.

Current output:

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

Desired output:

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

MemberID comes from the table a, MemberName comes from the tables a and b, and FruitName comes from the table b. Because I am outputting a lot of other columns from the 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"));

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

青春有你 2024-08-05 07:39:17

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;

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;
多情癖 2024-08-05 07:39:17

鉴于您使用的是 MySQL,我相信 GROUP_CONCAT 函数(可用的聚合函数)将完全满足您的需求。

Given that you're using MySQL, I believe one of the GROUP_CONCAT function (one of the available aggregate functions) will do exactly what you're looking for.

醉态萌生 2024-08-05 07:39:17
SELECT MemberID, MemberName, GROUP_CONCAT(FruitName SEPARATOR ',') FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY MemberID, MemberName;
SELECT MemberID, MemberName, GROUP_CONCAT(FruitName SEPARATOR ',') FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY MemberID, MemberName;
我偏爱纯白色 2024-08-05 07:39:17

一种方法是用 PHP(或您正在使用的任何语言)循环并构建输出。

从数据库获取行时或将其呈现给用户时连接水果。 我建议后者,因为这更多的是一个视图逻辑问题。

如果你想在 SQL 中执行此操作,你要么必须创建自己的聚合函数(我不相信 MySQL 内置了它,但我可能是错的)来连接字符串。 这并不是那么微不足道的事情。

如果您只获取一个成员,您也可以通过使用变量在 SQL 中执行此操作,我不确定确切的 MySQL 语法,但对于 MSSQL 来说,它会类似于:

DECLARE @frutis VARCHAR(MAX)

SELECT @fruits = ISNULL(@fruits + ', ' + FruitName, FruitName)
FROM ... 

SELECT @fruits

One way is to loop and build the output in PHP (or whatever language yo are using).

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.

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:

DECLARE @frutis VARCHAR(MAX)

SELECT @fruits = ISNULL(@fruits + ', ' + FruitName, FruitName)
FROM ... 

SELECT @fruits
埋情葬爱 2024-08-05 07:39:17

@亚当
我知道回答可能已经晚了,但我刚刚遇到了同样的问题,所以这是我的解决方案来扩展迈克的答案并解决多个相同的水果出现:

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

所以基本上你只需要在 GROUP_CONCAT 函数中添加“不同”。

我希望这有帮助。

@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:

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.

I hope that helps.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文