SQLite按album分组时如何按优先级获取对应status的path字段值

解决方案 #

你可以通过CASE WHEN配合COALESCE函数实现优先级取值，该写法兼容所有SQLite版本，修改后的查询语句如下：

SELECT
    COALESCE(
        MAX(CASE WHEN status = 'REC' THEN status END),
        MAX(CASE WHEN status = 'INFO' THEN status END)
    ) AS status,
    COUNT(idFileRemote) AS count_idFileRemote,
    ROUND(AVG(rating), 2) AS rating,
    album,
    COALESCE(
        MAX(CASE WHEN status = 'REC' THEN coverhash END),
        MAX(CASE WHEN status = 'INFO' THEN coverhash END)
    ) AS coverhash,
    COALESCE(
        MAX(CASE WHEN status = 'REC' THEN path END),
        MAX(CASE WHEN status = 'INFO' THEN path END)
    ) AS path
FROM tracks
GROUP BY album;

逻辑说明

• 用CASE WHEN筛选出对应status的字段值，非目标status的返回值为NULL
• COALESCE函数会按顺序返回第一个非NULL的值：先取status为REC的对应字段值，如果为NULL（分组内无REC记录），再取status为INFO的对应字段值
• 统计类聚合函数COUNT、AVG不受筛选影响，仍然计算分组内所有记录的统计结果

如果你使用的是SQLite 3.25.0及以上版本，也可以用窗口函数实现，逻辑更直观：

WITH ranked_tracks AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY album 
            ORDER BY CASE status 
                WHEN 'REC' THEN 1 
                WHEN 'INFO' THEN 2 
                ELSE 999 
            END ASC
        ) AS rn
    FROM tracks
)
SELECT 
    MAX(CASE WHEN rn = 1 THEN status END) AS status,
    COUNT(idFileRemote) AS count_idFileRemote,
    ROUND(AVG(rating), 2) AS rating,
    album,
    MAX(CASE WHEN rn = 1 THEN coverhash END) AS coverhash,
    MAX(CASE WHEN rn = 1 THEN path END) AS path
FROM ranked_tracks
GROUP BY album;

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