SQL多表连接问题：获取成员最多的6支乐队及对应最长歌曲

现有6张数据表：Albums、Bands、composers、members、musicians、songs，表结构如下：

• Albums: id_album | title | id_band | year
• Bands: id_band | name | style | origin
• composers: id_musician | id_song
• members: id_musician | id_band | instrument
• musicians: id_musician | name | birth | death | gender
• songs: id_song | title | duration | id_album

需求：编写SQL查询，获取成员数量最多的6支乐队，并查询这些乐队对应的最长歌曲时长及歌曲名称。

已实现的查询片段：

1. 获取成员最多的6支乐队：

SELECT bands.name, COUNT(id_musician) AS numberMusician
FROM bands
INNER JOIN members USING (id_band)
GROUP BY bands.name
ORDER BY numberMusician DESC
LIMIT 6;

2. 获取最长歌曲的查询：

SELECT MAX(duration), songs.title, id_album, id_band
FROM SONGs
INNER JOIN albums USING (id_album)
GROUP BY songs.title, id_album, id_band
ORDER BY MAX(duration) DESC

遇到的问题：尝试通过子查询或内连接结合两者时，因MAX函数的使用逻辑问题无法得到正确结果，需要正确的SQL语句。

可以通过先锁定成员最多的6支乐队，再关联歌曲、专辑表精准匹配每支乐队的最长歌曲（包括并列最长的情况），以下是两种可行写法：

方法1：使用CTE（推荐，可读性更高） #

-- 第一步：筛选成员数最多的6支乐队，保留乐队ID、名称、成员数
WITH top_bands AS (
    SELECT 
        b.id_band,
        b.name AS band_name,
        COUNT(m.id_musician) AS numberMusician
    FROM bands b
    INNER JOIN members m USING (id_band)
    GROUP BY b.id_band, b.name
    ORDER BY numberMusician DESC
    LIMIT 6
),
-- 第二步：计算这6支乐队各自的最长歌曲时长
band_max_duration AS (
    SELECT 
        ab.id_band,
        MAX(s.duration) AS max_duration
    FROM songs s
    INNER JOIN albums ab USING (id_album)
    INNER JOIN top_bands tb USING (id_band)
    GROUP BY ab.id_band
)
-- 第三步：关联所有表，匹配出每支乐队中时长等于最长时长的歌曲
SELECT 
    tb.band_name,
    tb.numberMusician,
    s.title AS song_title,
    s.duration AS song_duration
FROM top_bands tb
INNER JOIN albums ab USING (id_band)
INNER JOIN songs s USING (id_album)
INNER JOIN band_max_duration bmd 
    ON tb.id_band = bmd.id_band 
    AND s.duration = bmd.max_duration
ORDER BY tb.numberMusician DESC, s.duration DESC;

方法2：子查询写法（兼容不支持CTE的旧版SQL环境） #

SELECT 
    tb.band_name,
    tb.numberMusician,
    s.title AS song_title,
    s.duration AS song_duration
FROM (
    -- 筛选成员最多的6支乐队
    SELECT 
        b.id_band,
        b.name AS band_name,
        COUNT(m.id_musician) AS numberMusician
    FROM bands b
    INNER JOIN members m USING (id_band)
    GROUP BY b.id_band, b.name
    ORDER BY numberMusician DESC
    LIMIT 6
) tb
INNER JOIN albums ab USING (id_band)
INNER JOIN songs s USING (id_album)
INNER JOIN (
    -- 计算目标乐队的最长歌曲时长
    SELECT 
        ab.id_band,
        MAX(s.duration) AS max_duration
    FROM songs s
    INNER JOIN albums ab USING (id_album)
    WHERE ab.id_band IN (
        SELECT id_band FROM (
            SELECT b.id_band
            FROM bands b
            INNER JOIN members m USING (id_band)
            GROUP BY b.id_band
            ORDER BY COUNT(m.id_musician) DESC
            LIMIT 6
        ) temp
    )
    GROUP BY ab.id_band
) bmd 
    ON tb.id_band = bmd.id_band 
    AND s.duration = bmd.max_duration
ORDER BY tb.numberMusician DESC, s.duration DESC;

关键说明： #

1. 优先用乐队ID关联而非名称，避免因乐队重名导致的数据错误
2. 拆分逻辑为“筛选目标乐队→计算最长时长→匹配对应歌曲”，避免MAX函数分组时的逻辑混乱
3. 支持同乐队多首歌曲并列最长的场景，会全部列出

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