SQLite按作者藏书量等多条件排序的SQL语句写法求助

查询需求 #

• 优先按作者在图书馆内的藏书数量降序排序，藏书量越高排序越靠前
• 藏书量相同的情况下，按读完该作者所有书籍所需的最少天数升序排序
• 上述条件均一致时，按读完单本特定书籍所需的最少天数升序排序
• 最终按书籍名称（book字段）升序排序

原有SQL问题说明 #

1. GROUP BY book逻辑错误：按书名分组会合并同书名记录，丢失单本书数据，且非聚合字段直接查询会返回不确定值
2. 排序规则不符合需求：第一排序条件错误设置为author DESC，没有优先统计作者维度的藏书总量
3. 缺少作者维度的聚合指标计算，无法实现按作者藏书量、作者全量书籍阅读时长排序的需求

修正后SQL #

SELECT author, book 
FROM (
    SELECT 
        author, 
        book,
        COUNT(*) OVER (PARTITION BY author) AS author_book_total,
        SUM(total_number_of_pages) OVER (PARTITION BY author) * 1.0 / speed AS author_all_read_days,
        (total_number_of_pages - pages_read) * 1.0 / speed AS current_book_read_days
    FROM book_library
) AS temp
ORDER BY 
    author_book_total DESC,
    author_all_read_days ASC,
    current_book_read_days ASC,
    book ASC;

改动说明 #

• 用窗口函数PARTITION BY author统计每个作者的总藏书量、全量书籍总页数，既保留所有单本书的记录，又能获取作者维度的聚合指标用于排序
• 乘法*1.0是为了避免SQLite整数除法自动截断小数的问题，保证阅读时长计算精度
• 移除错误的GROUP BY子句，保证所有书籍记录正常输出
• 严格按照需求调整排序优先级，与要求完全匹配

数据表结构 #

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