SQLite技术求助：计算播放列表总时长并限制不超指定时长

首先得指出你当前SQL语句里的语法错误：你把聚合函数TOTAL(Length)和其他字段放在同一个括号里，这不符合SQL语法规范。正确的写法应该是把TOTAL(Length) AS TotalSongLength单独列出来，和其他字段用逗号分隔，但这里要注意——直接用TOTAL(Length)会计算所有符合条件的歌曲总时长，每一行都会重复这个总和，这显然不是你要的生成播放列表的逻辑。

接下来解决核心问题：如何生成总时长不超过指定值的播放列表。因为普通的WHERE子句无法直接过滤多行的总和，我们需要用更灵活的SQL技巧，这里针对SQLite（从你用c.execute来看应该是Python的sqlite3库）提供两种可行方案：

方案1：用窗口函数生成按顺序累加的播放列表 #

如果你只是想按某种规则（比如歌曲时长从小到大）累加歌曲，直到总时长接近目标值，可以用窗口函数计算累积时长，再筛选符合条件的记录：

SELECT 
    "Song Name", 
    "Artist", 
    "Genre", 
    "Album", 
    "Year", 
    Length,
    SUM(Length) OVER (ORDER BY Length ASC) AS CumulativeLength
FROM Songs
HAVING CumulativeLength <= :target_duration
ORDER BY Length ASC;

说明： #

• SUM(Length) OVER (ORDER BY Length ASC)会按歌曲时长从小到大的顺序，逐行计算累积总时长
• HAVING CumulativeLength <= :target_duration只保留累积时长不超过目标值的歌曲
• 在Python代码里，你需要用参数绑定传入目标时长，避免SQL注入：

  target_duration = 3600  # 比如目标1小时
  c.execute(query, (target_duration,))
  playlist = c.fetchall()
  

方案2：用递归CTE生成所有可能的有效组合（适合复杂场景） #

如果你想生成所有总时长不超过目标的歌曲组合（比如用户需要随机组合或多种可选列表），可以用递归公共表表达式（CTE）：

WITH RECURSIVE Playlist AS (
    -- 初始行：选第一首歌（这里按时长排序，你可以改成其他规则）
    SELECT 
        "Song Name", 
        "Artist", 
        "Genre", 
        "Album", 
        "Year", 
        Length AS SongLength,
        Length AS TotalLength,
        -- 用字符串记录已选歌曲，避免重复选择
        '"' || "Song Name" || '"' AS SelectedSongs
    FROM Songs
    ORDER BY Length ASC
    LIMIT 1
    UNION ALL
    -- 递归步骤：加入未选的歌曲，且总时长不超过目标
    SELECT 
        s."Song Name", 
        s."Artist", 
        s."Genre", 
        s."Album", 
        s."Year", 
        s.Length,
        p.TotalLength + s.Length AS TotalLength,
        p.SelectedSongs || ', "' || s."Song Name" || '"'
    FROM Playlist p
    JOIN Songs s ON s."Song Name" NOT IN (SELECT value FROM json_each('[' || p.SelectedSongs || ']'))
    WHERE p.TotalLength + s.Length <= :target_duration
)
-- 可以根据需要筛选，比如取总时长最接近目标的组合
SELECT * FROM Playlist
ORDER BY TotalLength DESC
LIMIT 1;

说明： #

• 递归CTE会逐步构建所有可能的有效歌曲组合，适合需要灵活组合的场景
• 注意：如果你的歌曲数量较多，这种方法可能会有性能问题，建议配合索引或限制递归深度

额外提示 #

• 确保Length字段是数值类型（比如整数、浮点数），否则SUM或TOTAL函数无法正确计算
• 如果你只是想验证某一组歌曲的总时长是否符合要求，可以用子查询计算总和：

  SELECT * FROM Songs
  WHERE "Song Name" IN ('Song1', 'Song2')
  HAVING TOTAL(Length) <= :target_duration;
  

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