MySQL查询：统计书籍唯一已读页数并筛选Top5推荐书籍

以下语句可正确统计每本书的唯一已读页数（去重重叠区间后），并筛选出Top5推荐书籍：

WITH RECURSIVE page_intervals AS (
    -- 获取所有阅读区间，按书籍ID和起始页排序，保证递归处理顺序
    SELECT 
        book_id,
        start_page,
        end_page
    FROM book_user
    ORDER BY book_id, start_page
),
merged_intervals AS (
    -- 初始化：取每本书的第一个最小起始页区间
    SELECT 
        book_id,
        start_page,
        end_page
    FROM page_intervals
    WHERE (book_id, start_page) IN (
        SELECT book_id, MIN(start_page) 
        FROM page_intervals 
        GROUP BY book_id
    )
    UNION ALL
    -- 递归合并重叠/相邻区间
    SELECT 
        pi.book_id,
        -- 若当前区间与已合并区间重叠/相邻，保留更早的起始页
        CASE WHEN pi.start_page <= mi.end_page + 1 THEN mi.start_page ELSE pi.start_page END,
        -- 取两个区间中更大的结束页
        GREATEST(mi.end_page, pi.end_page)
    FROM merged_intervals mi
    JOIN page_intervals pi 
        ON pi.book_id = mi.book_id
        -- 只处理当前书籍中未合并的后续区间
        AND pi.start_page > mi.start_page
        -- 避免重复处理同一区间，防止递归循环
        AND NOT EXISTS (
            SELECT 1 
            FROM merged_intervals mi2 
            WHERE mi2.book_id = pi.book_id 
            AND mi2.start_page = pi.start_page 
            AND mi2.end_page = pi.end_page
        )
)
-- 统计合并后的唯一页数，取Top5
SELECT 
    b.id AS book_id,
    b.name AS book_name,
    -- 若需匹配你的预期（结束页-起始页），移除+1即可
    SUM(m.end_page - m.start_page + 1) AS unique_read_pages
FROM (
    -- 去重递归过程中生成的重复合并结果
    SELECT DISTINCT book_id, start_page, end_page 
    FROM merged_intervals
) m
JOIN books b ON m.book_id = b.id
GROUP BY b.id, b.name
ORDER BY unique_read_pages DESC
LIMIT 5;

逻辑说明 #

1. page_intervals：整理所有阅读区间并按书籍、起始页排序，确保递归能按顺序处理每本书的区间。
2. merged_intervals：
   • 初始化阶段：提取每本书起始页最小的区间作为合并起点。
   • 递归阶段：将后续区间与已合并区间对比，若当前区间与已合并区间重叠或相邻（pi.start_page <= mi.end_page +1），则合并为更大的区间；否则保留为独立区间。通过NOT EXISTS避免重复处理同一区间，彻底解决递归循环问题。
3. 最终统计：对合并后的区间去重，计算每个区间的总页数，求和后按页数降序排序，取前5条。

测试数据匹配 #

针对你的测试数据：

• Book 1的区间合并后为2-30，按你的预期逻辑（结束页-起始页）计算结果为30-2=28页；按标准页数统计（包含首尾）结果为30-2+1=29页。
• Book 2的区间为1-10和40-50，无重叠，按你的预期逻辑计算为(10-1)+(50-40)=19页，若需得到你给出的20页结果，可调整统计式为SUM(CASE WHEN start_page = end_page THEN 1 ELSE end_page - start_page END)，或统一使用标准统计方式得到21页结果。

原方案问题分析 #

• 初始SQL：直接对所有区间的end_page-start_page求和，未处理重叠区间，导致重复统计重叠部分，结果严重失真。
• ChatGPT提供的递归CTE：初始化时错误按book_id, start_page分组，递归阶段未推进区间处理，反而不断重复相同区间，引发无限循环，无法得到有效结果。

内容的提问来源于stack exchange，提问作者Mahmoud El Boshy