SQL多表关联查询：如何将关联标签拼接为单个字段并去重？

需求 #

查询所有exercises条目，每条条目需对应关联的video文件，以及将所有关联标签拼接成单个字符串（标签存储在tags表，tag_linkage表建立exercises与tags的关联关系）。

理想输出格式 #

My Exercise Name, Somepath/video.mp4, TagName1|TagName2|TagName3
Another Exercise Name, Somepath/video.mp4, TagName2|TagName5|TagName6
and so on...

现有数据库表结构 #

CREATE TABLE `exercises` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `videos` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `tag_linkage` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `exercise_id` int(11) DEFAULT NULL,
  `tag_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

当前查询语句及问题 #

当前使用的查询语句：

SELECT 
exercises.name AS Exercise, 
videos.filename AS VideoName, 
tags.name as Tag,
FROM exercises
  INNER JOIN tag_linkage AS tl ON  exercises.id = tl.exercise_id
  INNER JOIN tags ON tags.id = tl.tag_id 
    LEFT JOIN videos
    ON exercises.video_id = videos.id    
    ORDER BY exercises.id 
    LIMIT 20000 
    OFFSET 0;

问题：该查询返回的结果中，每个标签对应一行记录，导致同一exercise出现多条重复行，示例如下：

My Exercise Name, Somepath/video.mp4, TagName1
My Exercise Name, Somepath/video.mp4, TagName2
My Exercise Name, Somepath/video.mp4, TagName3
Another Exercise Name, Somepath/video.mp4, TagName1
Another Exercise Name, Somepath/video.mp4, TagName3
Another Exercise Name, Somepath/video.mp4, TagName5
and so on...

需要去除重复行，并将同一exercise的所有标签拼接为单个列。

最终解决方案 #

使用GROUP_CONCAT(DISTINCT)函数实现标签拼接，结合GROUP BY分组去重，查询语句如下：

SELECT 
exercises.name as Exercise, 
videos.filename AS VideoName, 
GROUP_CONCAT(DISTINCT(tags.name) SEPARATOR ', ') Tags
FROM exercises
LEFT JOIN videos
ON exercises.video_id = videos.id
JOIN tag_linkage ON exercises.id = tag_linkage.exercise_id
JOIN tags ON tags.id = tag_linkage.tag_id
GROUP BY exercises.name
LIMIT 1000
OFFSET 0;

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