mysql - SQL-返回具有多个类别的记录

我在SQL中使用分组和聚合函数相对较新，我有下表：

CREATE TABLE `artists` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL );
CREATE TABLE `genres` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL);
CREATE TABLE `songs` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL, `artist_id` INT NOT NULL );
CREATE TABLE `songs_genres` (`song_id` INT NOT NULL, `genre_id` INT NOT NULL );

我希望回馈拥有多种流派歌曲的歌手。任何想法最欢迎！

到目前为止，我已经将所有内容链接在一起，但无法完全解决所需的分组/聚合：

select a.name as name, g.name as genre
from artists a inner join songs s on a.id = s.artist_id
inner join songs_genres sg on s.id = sg.song_id
inner join genres g on g.id = sg.genre_id

提前致谢。

最佳答案

这可能会有所帮助。它将发现多少流派有多个艺术家：

SELECT COUNT(a.Name) AS "No. of Artists",
       g.Name AS "Genre"
FROM Artists a
INNER JOIN songs s on a.id = s.artist_id
INNER JOIN songs_genres sg on s.id = sg.song_id
INNER JOIN genres g on g.id= sg.genre_id
GROUP BY g.Name

这将为您提供每个艺术家的流派数量：

SELECT COUNT(g.Name) AS "No. of Genres",
           a.Name AS "Artist"
FROM Artists a
INNER JOIN songs s on a.id = s.artist_id
INNER JOIN songs_genres sg on s.id = sg.song_id
INNER JOIN genres g on g.id= sg.genre_id
GROUP BY a.Name

添加HAVING子句将使您将结果范围缩小到不超过任何数量的Artist / Genres：

SELECT COUNT(g.Name) AS "No. of Genres",
               a.Name AS "Artist"
    FROM Artists a
    INNER JOIN songs s on a.id = s.artist_id
    INNER JOIN songs_genres sg on s.id = sg.song_id
    INNER JOIN genres g on g.id= sg.genre_id
    GROUP BY a.Name
HAVING Count(g.Name) > 1 -- or 2, or 10. This will skip over single-genre artists

您在这些查询中所做的工作是计算给定值按另一个值分组时出现的次数。

关于mysql - SQL-返回具有多个类别的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28509360/