sql 语句检索给定记录的结果列表（从其他表）？
我有以下架构：
TABLE bands
-> band_id
-> property1
-> property2
-> ...
TABLE tracks
-> band_id
-> track_id
曲目表通常将单个 band_id 映射到多个 track_id （每个乐队有多个曲目）。
我的目标是为匹配某些条件（在 property1、property2 等方面）的乐队构建格式为 (band_id、property1、property2、...、[该乐队的 track_ids 列表]) 的数据结构。
目前我正在做
SELECT band_id, property1, property2, ..., track_id
FROM bands, tracks
WHERE bands.property1 = xyz;
这会为匹配的乐队生成元组（band_id，property1，property2，...，track_id）；每个轨道一个元组。目前，我将其聚合（在我的非 SQL 代码中）给定频段的所有 track_ids 到一个列表中。但这似乎非常浪费，因为除了“propertyN”之外的所有值都会为每个单独的频段多次返回。
我想到的替代方案是首先获取匹配乐队的 band_ids 和属性，然后对每个乐队的 track_ids 发出单独的查询。但这反过来意味着为每个频段发出单独的查询 - 而且有数以万计的频段，因此这可能不是很聪明。
有更好的方法吗？
我一直在研究 GROUP BY ，这似乎是我感兴趣的总体方向，但我找不到一个聚合函数，该函数基本上会为表中的每个匹配记录返回值列表（在本例中为 track_ids）乐队。
I have the following schema:
TABLE bands
-> band_id
-> property1
-> property2
-> ...
TABLE tracks
-> band_id
-> track_id
The tracks table typically maps a single band_id onto multiple track_ids (there are multiple tracks for each band).
My goal is to build, for the bands that match certain conditions (in terms of property1, property2, etc), a data structure of the format (band_id, property1, property2, ..., [list of track_ids for that band]).
Currently I am doing
SELECT band_id, property1, property2, ..., track_id
FROM bands, tracks
WHERE bands.property1 = xyz;
This generates tuples (band_id, property1, property2, ..., track_id) for the matching bands; one tuple for each track. Currently I take this and aggregate (in my non-sql code) all track_ids for a given band into a list. But it seems very wasteful, since all but the 'propertyN' values are getting returned multiple times for each single band.
The alternative I thought of was to first get the band_ids and properties for the matching bands, and then issue a separate query for the track_ids for each band. But this, in turn, means issuing a separate query for each band -- and there are tens of thousands of them, so this probably is not very smart.
Is there a better way to do this?
I have been looking at GROUP BY and that seems to be the general direction I am interested in, but I couldn't find an aggregate function that would basically return a list of values (in this case, track_ids) for each matching record in table bands.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以以 (band_id, [该乐队的 track_ids 列表作为逗号分隔的字符串]) 形式返回数据，但这不是 SQL 方式。看看 GROUP_CONCAT (对于 MySQL）。
尽管您应该使用显式的 JOIN 关键字，但您当前的做法通常更好。
从数据库中读取结果后，您可以将结果放入您选择的结构中。
You can return data in the form (band_id, [list of track_ids for that band as comma separated string]) but it's not the SQL way. Look at GROUP_CONCAT (for MySQL).
The way you are currently doing it is usually better, although you should use an explicit JOIN keyword.
You can put the results into the structure of your choice once you have read them from the database.
如果你想生成一个字符串并且你正在使用 MySQL，那么有一个 GROUP_CONCAT 聚合函数，但至少在 SQL Server 中没有简单的等效函数。对于 SQL Server，您可以使用 FOR XML 查询来生成具有所需分组类型的 XML 输出，但我不确定这通常有多大帮助。 （在我的许多应用程序中，我使用 FOR XML 查询从数据库中提取数据，然后使用 XSLT 将其转换为 HTML 以在 Web 上显示，但我知道这是一个相当具体的解决方案，而不是通用的解决方案。）
If you want to produce a string and you're using MySQL there's a GROUP_CONCAT aggregation function but there's no simple equivalent in SQL Server, at least. For SQL Server you can use FOR XML queries to produce XML output with the sort of grouping that you want but I'm not sure how helpful that is in general. (In many of my applications I use FOR XML queries to extract data from a database and then XSLT to transform it into HTML for display on the web, but I'm aware that this is a fairly specific solution rather than a general one.)