MySQL CASE分组查询中指定显示特定行artist和name字段问题

问题分析 #

你当前的SQL语句在分组时，artist和name字段既不在分组键中，也未使用聚合函数，数据库会随机选取分组内任意一条记录的对应值，这就是结果中显示artist2而非目标artist1的原因。要固定显示persistent_id = 'C660FF302599636B'对应的artist和name，需要在聚合时明确指定取值逻辑。

解决方案一：使用条件聚合函数 #

通过CASE语句筛选出目标persistent_id对应的artist和name，再用MAX()或MIN()聚合（分组内仅目标ID对应的该字段有值，其余为NULL，聚合函数会自动忽略NULL），即可固定获取到artist1和name1：

SELECT 
    MAX(CASE WHEN persistent_id = 'C660FF302599636B' THEN artist END) AS artist,
    MAX(CASE WHEN persistent_id = 'C660FF302599636B' THEN name END) AS name,
    SUM(play_count) AS total_play_count,
    CASE 
        WHEN persistent_id IN ('C660FF302599636B', '0BDDB14AE2CC4675') 
        THEN 'C660FF302599636B' 
        ELSE persistent_id 
    END AS persistent_id
FROM tracks
GROUP BY 
    CASE 
        WHEN persistent_id IN ('C660FF302599636B', '0BDDB14AE2CC4675') 
        THEN 'C660FF302599636B' 
        ELSE persistent_id 
    END
ORDER BY total_play_count DESC;

解决方案二：先分组求和再关联原表 #

先通过CTE计算分组后的总播放量，再关联原表获取目标persistent_id对应的artist和name，逻辑更直观：

WITH grouped_tracks AS (
    SELECT 
        CASE 
            WHEN persistent_id IN ('C660FF302599636B', '0BDDB14AE2CC4675') 
            THEN 'C660FF302599636B' 
            ELSE persistent_id 
        END AS group_id,
        SUM(play_count) AS total_play_count
    FROM tracks
    GROUP BY group_id
)
SELECT 
    t.artist,
    t.name,
    g.total_play_count,
    g.group_id AS persistent_id
FROM grouped_tracks g
LEFT JOIN tracks t ON g.group_id = t.persistent_id
ORDER BY g.total_play_count DESC;

两种方法都能得到你期望的结果：

artist1 name1   92  C660FF302599636B
artist3 name3   10  000ABA9AB1DCD725
artist4 name4   10  00361E574E776781

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