用户提交他/她阅读的开始和结束页面的间隔 书请注意，用户可以为同一本书提交多个间隔. 我需要查询宣布最推荐的五本书在系统中，这是根据有多少个独特的页面被读取， 在第一个操作中提交了间隔的所有用户(按读页数最多的书到读页数最少的书排序).
book_user表是我需要查询的数据透视表，因此如何获得插入记录的结果:
阅读间隔:
User 1 read from page 10 to page 30 in Book 1
User 2 read from page 2 to page 25 in Book 1
User 1 read from page 40 to page 50 in Book 2
User 3 read from page 1 to page 10 in Book 2
The most read books results:
Book 1 -> 28 pages
Book 2 -> 20 pages
我try 这个查询:
select 'book_id',books.name as book_name,SUM(end_page - start_page) AS num_of_read_pages FROM book_user JOIN books ON books.id=book_user.book_id GROUP BY book_id ORDER BY num_of_read_pages DESC;
但它并没有得到重叠间隔的唯一页面
当我问chatgpt时，它给了我这个查询，它是递归的，但是它不工作，它只是循环
WITH RECURSIVE cte AS (
SELECT book_id, MIN(start_page) AS start_page, MAX(end_page) AS end_page
FROM book_user
GROUP BY book_id, start_page
UNION ALL
SELECT cte.book_id, cte.start_page, cte.end_page
FROM cte
JOIN book_user ON cte.book_id = book_user.book_id AND cte.start_page <= book_user.start_page AND cte.end_page >= book_user.end_page
)
SELECT book_id, SUM(end_page - start_page + 1) AS total_pages
FROM cte
GROUP BY book_id
ORDER BY total_pages DESC;