Mysql - 加入多个ID并结束连接列?
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.
release_artist_ids可以包含一个或多个与艺术家表对应的艺术家ID。如果同一个release_id上有多个艺术家的特征,那么我用逗号分隔了ID。
So an example release row:
这是一个示例发布行:
1 -- 2,5 -- Example Release
1 - 2,5 - 示例发布
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.
我设法使用IN(release.release_artist_ids)加入,但这只会带回一个艺术家的名字。我想将所有涉及的艺术家合并到一个返回的列中,用管道分隔,以便稍后进行格式化。
1 个解决方案
#1
3
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
.
我建议你改变你的数据库设计,让你有一个名为release_artist的连接表来模拟艺术家和发行版之间的多对多关系。该表应该有两列:release_id和artist_id。
If you can't change the database design, you can use FIND_IN_SET
but it will be slow:
如果无法更改数据库设计,可以使用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
更多相关文章
- mysql锁机制之示例讲解
- MySQL 示例数据库sakila
- MySQL官网示例数据库emploees分析使用
- 高德地图api接口poi检索示例----并在信息框显示经纬度
- Objective-C方法/函数调用。 (来自javascript示例)
- 有一个简单但有用的jquery.JsPlumb示例吗?
- 获取错误“ValueError:int()的无效文字,基数为10:'3128;'在运行Tensor
- python的接口实现zope.interface示例
- Python套接字代理示例,不断收到调用bind()的错误..为什么?