SQL多表查询优化：按艺术家聚合视频、按视频聚合关联艺术家

已关联三个数据表，但查询结果存在重复：

• 当艺术家有多部音乐视频时，艺术家姓名重复
• 当音乐视频有多位艺术家参演时，视频标题重复

表结构 #

CREATE TABLE videos (id INT UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255));
CREATE TABLE artists (id INT UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));
CREATE TABLE roles (id INT UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY, video_id INT, artist_id INT);

测试数据 #

INSERT INTO videos (title) VALUES ('One vs Many'), ('Nikola Tesla'), ('Seat @ the Table'), ('Vinyl');
INSERT INTO artists (name) VALUES ('Mental Stamina'), ('Mozay Calloway'), ('Melatto');
INSERT INTO roles (video_id,artist_id) VALUES (1,1), (2,1), (3,2), (3,3), (4,2);

当前查询语句 #

SELECT artists.name, videos.title FROM roles 
INNER JOIN artists on artists.id = roles.artist_id 
INNER JOIN videos on videos.id = roles.video_id 
GROUP BY roles.artist_id, roles.video_id 
ORDER BY roles.artist_id;

当前输出 #

• Mental Stamina - One vs Many
• Mental Stamina - Nikola Tesla
• Mozay Calloway - Seat @ the Table
• Mozay Calloway - Vinyl
• Melatto - Seat @ the Table

期望输出1：按艺术家展示关联视频 #

• Mental Stamina - One vs Many, Nikola Tesla
• Mozay Calloway - Seat @ the Table, Vinyl
• Melatto - Seat @ the Table

期望输出2：按视频展示关联艺术家 #

• One vs Many - Mental Stamina
• Nikola Tesla - Mental Stamina
• Seat @ the Table - Mozay Calloway, Melatto
• Vinyl - Mozay Calloway

解决方案 #

使用MySQL的GROUP_CONCAT()函数实现分组字符串拼接，即可得到聚合后的结果：

实现期望输出1的SQL #

SELECT 
    artists.name, 
    GROUP_CONCAT(videos.title SEPARATOR ', ') AS videos
FROM roles 
INNER JOIN artists ON artists.id = roles.artist_id 
INNER JOIN videos ON videos.id = roles.video_id 
GROUP BY artists.id, artists.name 
ORDER BY artists.id;

实现期望输出2的SQL #

SELECT 
    videos.title, 
    GROUP_CONCAT(artists.name SEPARATOR ', ') AS artists
FROM roles 
INNER JOIN artists ON artists.id = roles.artist_id 
INNER JOIN videos ON videos.id = roles.video_id 
GROUP BY videos.id, videos.title 
ORDER BY videos.id;

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