PostgreSQL JOIN查询后按图书维度分页及多字段排序问题求助

核心思路是先确定每页要展示的图书集合，再关联库存数据并在组内排序，避免直接关联后分页导致的同一图书跨页问题。

一、基础实现：按bookId升序分页，组内按unitPrice排序 #

先给每本图书分配连续排名，再根据排名筛选对应页的图书，最后关联库存并排序：

WITH ranked_books AS (
    SELECT 
        book_id,
        -- 按bookId升序生成图书排名
        DENSE_RANK() OVER (ORDER BY book_id) AS book_rank
    FROM Book
)
SELECT 
    b.book_id,
    b.book_name, -- 替换为你的Book表实际字段
    s.store_id,
    s.unit_price,
    s.stock_quantity -- 替换为你的StockInStore表实际字段
FROM ranked_books rb
JOIN Book b ON rb.book_id = b.book_id
JOIN StockInStore s ON b.book_id = s.book_id
-- 分页逻辑：第N页时，将1替换为N，每页2本
WHERE rb.book_rank BETWEEN ((1-1)*2 + 1) AND (1*2)
-- 先按图书分组保证同书记录不拆分，组内按unitPrice排序
ORDER BY b.book_id, s.unit_price;

二、进阶：按图书的单价维度排序分页，组内仍按unitPrice排序 #

如果需要先按图书的整体单价（比如最高单价、平均单价）排序分页，再在组内按库存单价排序，只需调整排名的生成逻辑：

WITH ranked_books AS (
    SELECT 
        book_id,
        -- 按图书的最高库存单价降序生成排名，可替换为AVG(unit_price)等聚合逻辑
        DENSE_RANK() OVER (ORDER BY (SELECT MAX(unit_price) FROM StockInStore WHERE book_id = Book.book_id) DESC) AS book_rank
    FROM Book
)
SELECT 
    b.book_id,
    b.book_name,
    s.store_id,
    s.unit_price,
    s.stock_quantity
FROM ranked_books rb
JOIN Book b ON rb.book_id = b.book_id
JOIN StockInStore s ON b.book_id = s.book_id
WHERE rb.book_rank BETWEEN ((1-1)*2 + 1) AND (1*2)
-- 先按图书的排序依据分组，组内按unitPrice排序
ORDER BY (SELECT MAX(unit_price) FROM StockInStore WHERE book_id = b.book_id) DESC, s.unit_price;

关键注意事项 #

• 分页参数替换：将SQL中的1替换为当前页码，每页2本的逻辑是(page-1)*2 +1到page*2，如果每页数量调整，修改数字2即可。
• 无库存图书处理：如果需要包含无库存的图书，将JOIN StockInStore改为LEFT JOIN StockInStore，并处理库存字段的NULL值。
• 排序逻辑灵活调整：排名的ORDER BY子句可以替换为任意图书维度的排序字段（如出版日期、销量等），只需保证每个图书对应唯一的排序依据即可。

内容的提问来源于stack exchange，提问作者Nguyen Quoc Bao