SQL实现240张图片按每页9张分页展示的技术咨询

Nice job laying the groundwork with your query! Your PageGrpNo calculation already correctly groups images into sets of 9—perfect for your pagination needs. Let's refine this to make it functional and address a few edge cases.

How Your Current Query Works #

Your PageGrpNo uses (ROW_NUMBER() - 1)/9 which:

• Assigns 0 to the first 9 images (rows 1-9)
• Assigns 1 to the next 9 (rows 10-18)
• Continues this pattern through all 240 images. For 240 images, this gives you 27 total pages (26 full pages of 9, plus one final page with 6 images).

Refined Query for Targeted Page Selection #

First, we'll fix a minor issue: your subquery returns duplicate person_id columns (one from People, one from PeoplePicture). Renaming one avoids ambiguity errors. Then we'll wrap the query to filter by PageGrpNo for specific pages:

-- Fetch a specific page (replace 0 with your target page number; starts at 0)
SELECT *
FROM (
    SELECT *, 
        (ROW_NUMBER() OVER (ORDER BY person_id) - 1) / 3 AS RowGrpNo,
        (ROW_NUMBER() OVER (ORDER BY person_id) - 1) % 3 AS ColGrpNo,
        (ROW_NUMBER() OVER (ORDER BY person_id) - 1) / 9 AS PageGrpNo
    FROM (
        SELECT DISTINCT
            People.person_id,
            People.ActivePassive,
            PeoplePicture.person_id AS pp_person_id, -- Rename duplicate column
            PeoplePicture.picture_id,
            PeoplePicture.Picture
        FROM People
        RIGHT JOIN PeoplePicture ON People.person_id = PeoplePicture.person_id
        WHERE People.ActivePassive = 'Active'
    ) AS image_data
) AS paginated_images
WHERE PageGrpNo = 0; -- Example: Get first page (images 1-9)

Adjusting Page Numbering to Start at 1 #

If you prefer page numbers that start at 1 (instead of 0), modify the PageGrpNo calculation to add 1:

(ROW_NUMBER() OVER (ORDER BY person_id) - 1) / 9 + 1 AS PageGrpNo

Then to fetch the first page, use WHERE PageGrpNo = 1, the 27th page uses WHERE PageGrpNo = 27.

Calculate Total Pages #

To get the total number of pages for your 240 images, run this quick query:

SELECT CEIL(COUNT(*) / 9.0) AS total_pages
FROM (
    SELECT DISTINCT
        People.person_id,
        PeoplePicture.picture_id
    FROM People
    RIGHT JOIN PeoplePicture ON People.person_id = PeoplePicture.person_id
    WHERE People.ActivePassive = 'Active'
) AS total_images;

This will return 27 as expected for 240 images.

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