介质表:
CREATE TABLE $media_table (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(300) DEFAULT NULL,
`options` longtext DEFAULT NULL,
PRIMARY KEY (`id`)
}
表示例:
id title options
--------------------------
1 video ...
2 video ...
3 audio ...
类别表:
CREATE TABLE $media_taxonomy_table (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(15) DEFAULT NULL,
`title` varchar(300) DEFAULT NULL,
`media_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`)
}
类型可以是“category”或“tag”（我猜这可能是enum）
表示例:
id type title media_id
---------------------------------------
1 category rock 1
2 category trance 1
3 category trance 2
4 category rock 3
5 tag silent 1
5 tag loud 1
6 tag foo 2
我正在尝试从$MEDIA_TAXONOMY_TABLE中对category和tag进行GROUP_CONCAT。
此查询将返回仅包含类别的GROUP_CONCAT
SELECT mt.id, mt.title, mt.options, GROUP_CONCAT(mtt.title ORDER BY mtt.title ASC SEPARATOR ', ') as category
FROM $media_table as mt
LEFT JOIN $media_taxonomy_table as mtt
ON mt.id = mtt.media_id AND mtt.type='category'
WHERE playlist_id = %d
GROUP BY mt.id
收到的结果:
id title options category
----------------------------------------
1 video ... rock, trance
2 video ... trance
3 audio ... rock
预期（我也需要标记）:
id title options category tag
--------------------------------------------------------------
1 video ... rock, trance silent, load
2 video ... trance foo
3 audio ... rock
您可以执行条件聚合:
SELECT
mt.id,
mt.title,
mt.options,
GROUP_CONCAT(CASE WHEN mtt.type = 'category' THEN mtt.title END ORDER BY mtt.title SEPARATOR ', ') as categories,
GROUP_CONCAT(CASE WHEN mtt.type = 'tag' THEN mtt.title END ORDER BY mtt.title SEPARATOR ', ') as tags
FROM $media_table as mt
LEFT JOIN $media_taxonomy_table as mtt ON mt.id = mtt.media_id
WHERE mt.playlist_id = %d
GROUP BY mt.id, mt.title, mt.options
似乎需要另一个group_concat，因此需要另一个join
SELECT mt.id
, mt.title
, mt.options
, GROUP_CONCAT(mtt.title ORDER BY mtt.title ASC SEPARATOR ', ') as category
, GROUP_CONCAT(mtt2.title ORDER BY mtt.title ASC SEPARATOR ', ') as tag
FROM $media_table as mt
LEFT JOIN $media_taxonomy_table as mtt
ON mt.id = mtt.media_id AND mtt.type='category'
LEFT JOIN $media_taxonomy_table as mtt2
ON mt.id = mtt.media_id AND mtt.type='tag'
WHERE playlist_id = %d
GROUP BY mt.id
