SQL双列GROUP BY搭配MAX函数问题求助

你的原始查询之所以会返回艺术家所有作品的时长，核心问题在于GROUP BY artist.name, recording.name——这个分组条件会把每个艺术家的每一条独立录音都当成一个分组，所以自然会列出所有作品。要实现“获取第二长录音”的需求，我们可以用窗口函数来给每个艺术家的录音按时长排序，然后筛选出排名第二的记录。

先修正你原查询里的关联错误（原SQL中artist_credit_name和artist的关联条件写错了，应该用artist_credit_name.artist = artist.id而不是artist_credit_name.artist_credit=artist.id），然后用CTE结合窗口函数来实现：

WITH ranked_recordings AS (
    SELECT 
        artist.name AS artist_name,
        recording.name AS recording_name,
        recording.length,
        -- 按艺术家分组，给录音时长从长到短排名
        -- 若需要处理并列时长（比如两个录音都是第二长），把ROW_NUMBER()换成RANK()
        ROW_NUMBER() OVER (PARTITION BY artist.id ORDER BY recording.length DESC) AS length_rank
    FROM recording
    INNER JOIN artist_credit ON recording.artist_credit = artist_credit.id
    INNER JOIN artist_credit_name ON artist_credit.id = artist_credit_name.artist_credit
    INNER JOIN artist ON artist_credit_name.artist = artist.id
    WHERE artist.gender = 1
)
SELECT artist_name, recording_name, length
FROM ranked_recordings
WHERE length_rank = 2
ORDER BY artist_name;

关键说明： #

• 窗口函数的作用：ROW_NUMBER() OVER (PARTITION BY artist.id ORDER BY recording.length DESC)会给每个艺术家的录音单独排序，时长最长的排第1，次长的排第2，以此类推。用artist.id分组比artist.name更可靠，避免不同艺术家同名导致的错误。
• 处理并列时长：如果你的场景中存在多个录音时长相同且都是第二长的情况，把ROW_NUMBER()换成RANK()，这样所有并列第二的录音都会被返回；如果只需要其中任意一个，保留ROW_NUMBER()即可。
• 指定单个艺术家：如果只需要查询某一位特定艺术家的第二长录音，只需在CTE的WHERE子句中添加艺术家的筛选条件，比如AND artist.name = '你的目标艺术家名字'或者AND artist.id = 123（用ID更准确）。

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