Mysql - 连接多个 ID 并连接连接列？

发布于 2024-12-05 01:41:05 字数 526 浏览 0 评论 0原文

不确定我的问题标题有多准确，但让我更好地解释一下。

我有两个表：艺术家、发行版

table artist
  artist_id, 
  artist_name

table release
  release_id,
  release_artist_ids,
  release_name

还有更多列名称，但这些是我的问题所涉及的。

release_artist_ids可以包含与艺术家表相对应的一个或多个艺术家ID。如果不止一位艺术家在同一个 release_id 上出现，那么我用逗号分隔 ID。

因此，发布行示例如下：

1 -- 2,5 -- 示例发布

我想做的是列出发行版并加入艺术家表以获取发行版中涉及的艺术家姓名。

我设法使用 IN (release.release_artist_ids) 加入，但这只会带回一个艺术家姓名。我想将所有涉及的艺术家合并到一个返回的列中，用管道分隔，以便稍后格式化。

原文

Not sure how accurate my question title is but let me explain a little better.

I have two tables: artists, releases

table artist
  artist_id, 
  artist_name

table release
  release_id,
  release_artist_ids,
  release_name

There are more column names, but these are the ones involved in my problem.

release_artist_ids can contain one or more artist IDs that correspond to the artist table. If more than one artist features on the same release_id then I had comma separated the IDs.

So an example release row:

1 -- 2,5 -- Example Release

What I would like to do is list the releases and join the artist table to fetch the artist names involved in the release.

I managed to join using IN (release.release_artist_ids) but this only brings back one artist name. I would like to merge all involved artists into one returned column, separated by a pipe so I can format later on.

分享到QQ

分享到微博

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

发布评论

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

欢你一世 2024-12-12 01:41:05

我建议您更改数据库设计，以拥有一个名为 release_artist连接表 > 模拟艺术家和发行之间的多对多关系。该表应有两列：release_id 和 artist_id。

如果无法更改数据库设计，可以使用 FIND_IN_SET 但会很慢：

SELECT
   release.release_id,
   GROUP_CONCAT(artist.artist_name, '|') AS artist_names
FROM artist 
JOIN release
ON FIND_IN_SET(artist.id, release.release_artist_ids)
GROUP BY release.release_id

I suggest that you change your database design to have a join table called release_artist that models the many-to-many relationship between artists and releases. This table should have two columns: release_id and artist_id.

If you can't change the database design, you can use FIND_IN_SET but it will be slow:

SELECT
   release.release_id,
   GROUP_CONCAT(artist.artist_name, '|') AS artist_names
FROM artist 
JOIN release
ON FIND_IN_SET(artist.id, release.release_artist_ids)
GROUP BY release.release_id

回复收藏 0 原文

~没有更多了~