如何获取列表中的列表（类似于多维数组）？
这是我经常遇到的问题，我的解决方案总是要运行大量查询，但我觉得一定有更好的方法。这是我用于此示例的两个表：
艺术家
编号
姓名歌曲
编号
艺术家ID
姓名
流派
该表的链接如下：
Artists.id <->歌曲.artistID
我想获取所有艺术家的列表，同时获取每个艺术家的所有歌曲的列表。我想避免结果集中出现重复的艺术家姓名，这样我就不必清理 PHP 中的数据集。这是我希望数据采用的格式的示例：（
array(0 => array('name' => 'Artist A'
, 'songs' => array(0 => array('name' => 'Song 1'
, 'genre' => 'rock'
)
)
1 => array('name' => 'Song 2'
, 'genre' => 'rock'
)
)
)
1 => array('name' => 'Artist B'
, 'songs' => array(0 => array('name' => 'Song 3'
, 'genre' => 'rap'
)
)
1 => array('name' => 'Song 4'
, 'genre' => 'rap'
)
)
)
)
我知道这不是 MySQL 格式，但这是我可以解释的最接近的方式）
这可能吗？如果是这样，怎么办？
谢谢！
This is a problem I often run into, and my solution has always to run numerous queries, but I feel there must be a better way. Here are my two tables for this example:
Artists
id
nameSongs
id
artistID
name
genre
The table are linked as such:
artists.id <-> songs.artistID
I'd like to fetch a list of all artists and at the same time fetch a list of all songs per artist. I'd like to avoid duplicate artist names in my result set, so that I don't have to clean the data set in PHP. Here's an example of the format I'd like the data to be in:
array(0 => array('name' => 'Artist A'
, 'songs' => array(0 => array('name' => 'Song 1'
, 'genre' => 'rock'
)
)
1 => array('name' => 'Song 2'
, 'genre' => 'rock'
)
)
)
1 => array('name' => 'Artist B'
, 'songs' => array(0 => array('name' => 'Song 3'
, 'genre' => 'rap'
)
)
1 => array('name' => 'Song 4'
, 'genre' => 'rap'
)
)
)
)
(I know this isn't a MySQL format, but it's the closest way I could explain)
Is this possible? If so, how?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我能建议的最好的办法是查询父记录，然后使用 in（父 ID 列表）查询查询子记录。这将为您提供两个数据集。一份给父母，一份给孩子。在客户端，您可以获取父 ID 并获取一组子记录。
The best thing I can suggest is a query for the parent records followed by a query for the children using an in (parent id list) query. This will give you two data sets. One for the parents and one for the children. On the client side you can then take a parent id and get a set of children records.
选项 1：
PROs：
没有从数据库传递不必要的信息。
缺点：
可能会运行很多很多查询。
选项 2：
PROs:
只需要一项查询。
缺点：
可能会从数据库传递大量不必要的数据。
Option 1:
PROs:
No unnecessary information being passed from the database.
CONs:
Potentially lots and lots of queries being run.
Option 2:
PROs:
Only one query required.
CONs:
Potentially lots and lots of unnecessary data being passed from the db.
恐怕不可能做你想做的事，因为 SQL 本质上是一种基于集合的语言。您可以通过使用表连接在一次调用中获取您正在查找的所有信息，但之后，您必须解析它们并将它们存储到您提到的结构中。
I am afraid it's not possible to do what you want because SQL is inherently a set-based language. You can get all the info that you are looking for in one call via the use of table joins, but after that, you would have to parse them and store them into the structure that you mentioned.