如何从Toxi风格数据库中查询歌曲对应的标签名称？

现有三张关联表：

• Songs表：存储歌曲基础信息

| index   title     ...
-----------------------
| 'a001'  'title1'  ...
| 'a002'  'title2'  ...
...

• Tagmap表：歌曲与标签的关联映射

| index   item_index   tag_index
--------------------------------
|     1       'a001'      't001'
|     2       'a001'      't003'
|     3       'a001'      't004'
|     4       'a002'      't003'
|     5       'a002'      't005'
...

• Tags表：标签详细信息

| tag_index         name
------------------------
|    't001'        'foo'
|    't002'        'bar'
|    't003'     'foobar'
...

需要编写SQL查询实现：

1. 筛选指定歌曲（如WHERE title = "abc"）
2. 将该歌曲的所有标签名称聚合在同一行，输出格式示例：

[0]: {index: 'a001', title: 'title1', tags: ['foo', 'foobar']}
[1]: {index: 'a002', title: 'title2', tags: ['foobar', 'something']}

当前已有SQL仅能返回标签索引，无法获取标签名称，现有语句：

SELECT s.index, s.title, s.licensable, GROUP_CONCAT(tm.tag_index as tags) FROM songs s
LEFT JOIN tagmap tm ON s.index = tm.item_index
WHERE s.is_public = 1 GROUP BY s.catalogue_index ORDER BY s.release_date DESC

注：Songs表与Tags表无直接关联，需通过Tagmap表作为中间关联。

解决方案 #

在现有查询基础上，通过Tagmap关联到Tags表，聚合标签的name字段即可，修改后的SQL如下：

SELECT 
    s.index, 
    s.title, 
    s.licensable, 
    GROUP_CONCAT(t.name SEPARATOR ', ') AS tags
FROM songs s
LEFT JOIN tagmap tm ON s.index = tm.item_index
LEFT JOIN tags t ON tm.tag_index = t.tag_index
WHERE s.is_public = 1 
  -- 可添加指定歌曲筛选条件，例如：AND s.title = "abc"
GROUP BY s.index, s.title, s.licensable 
ORDER BY s.release_date DESC

关键修改说明： #

1. 新增LEFT JOIN tags t ON tm.tag_index = t.tag_index，通过Tagmap的tag_index关联Tags表获取标签名称
2. 将GROUP_CONCAT(tm.tag_index)替换为GROUP_CONCAT(t.name)，聚合标签名称而非索引
3. 调整GROUP BY字段，确保包含所有非聚合查询字段（适配严格SQL模式要求）
4. 可选：通过SEPARATOR自定义标签分隔符，默认逗号，可按需调整

如果需要将标签格式化为数组形式（如示例中的['foo', 'foobar']），可以拼接引号和括号：

SELECT 
    s.index, 
    s.title, 
    s.licensable, 
    CONCAT('[', GROUP_CONCAT(CONCAT('"', t.name, '"') SEPARATOR ', '), ']') AS tags
FROM songs s
LEFT JOIN tagmap tm ON s.index = tm.item_index
LEFT JOIN tags t ON tm.tag_index = t.tag_index
WHERE s.is_public = 1 
GROUP BY s.index, s.title, s.licensable 
ORDER BY s.release_date DESC

内容的提问来源于stack exchange，提问作者traxx2012