SQLite如何输出列名以查询各流媒体平台最高收入电影

你当前使用的是平台作为列的宽表结构，因此无法直接将平台名作为字段值输出，首先需要将宽表转为长表，再关联票房表计算各平台最高收入电影即可实现需求。

适配最新版SQLite的查询语句 #

WITH platform_movies AS (
    -- 宽表转长表，将列名转为Platform字段的取值
    SELECT Title, 'Netflix' AS Platform FROM StreamedMovies WHERE Netflix = 1
    UNION ALL
    SELECT Title, 'Hulu' AS Platform FROM StreamedMovies WHERE Hulu = 1
    UNION ALL
    SELECT Title, 'Disney' AS Platform FROM StreamedMovies WHERE Disney = 1
),
ranked_movies AS (
    -- 关联票房数据，按平台分组对收入做排序
    SELECT 
        pm.Platform,
        pm.Title,
        md.Revenue,
        RANK() OVER (PARTITION BY pm.Platform ORDER BY md.Revenue DESC) AS revenue_rank
    FROM platform_movies pm
    JOIN MovieData md ON pm.Title = md.Title
)
-- 过滤出每个平台收入最高的电影，存在并列最高时会全部返回
SELECT Platform, Title, Revenue
FROM ranked_movies
WHERE revenue_rank = 1;

低版本SQLite（3.25.0之前不支持窗口函数）兼容方案 #

WITH platform_movies AS (
    SELECT Title, 'Netflix' AS Platform FROM StreamedMovies WHERE Netflix = 1
    UNION ALL
    SELECT Title, 'Hulu' AS Platform FROM StreamedMovies WHERE Hulu = 1
    UNION ALL
    SELECT Title, 'Disney' AS Platform FROM StreamedMovies WHERE Disney = 1
)
SELECT 
    pm.Platform,
    pm.Title,
    md.Revenue
FROM platform_movies pm
JOIN MovieData md ON pm.Title = md.Title
WHERE md.Revenue = (
    SELECT MAX(md2.Revenue)
    FROM platform_movies pm2
    JOIN MovieData md2 ON pm2.Title = md2.Title
    WHERE pm2.Platform = pm.Platform
);

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