我的查询中似乎有一个错误,当我增加OFFSET值时,会导致结果重复。它应该每页显示20个结果。只有3个结果与我的查询匹配,但由于某种原因,其中一些结果会重复出现,尽管它们应该只出现在查询结果的第一页上(例如,请参见下文(。
SELECT all_users_subbed_to.*,
(SELECT COUNT(*)
FROM subscribers s2
WHERE
s2.publisher_id = all_users_subbed_to.publisher_id)
AS subscribers_sub_count
FROM
(SELECT publisher_id,
subscriber_id, u2.username AS username,
u2.user_photo AS user_photo
FROM subscribers s
INNER JOIN users u
ON (u.id = s.subscriber_id)
INNER JOIN users u2
ON (u2.id = s.publisher_id)
WHERE subscriber_id = 80
)
AS all_users_subbed_to
ORDER BY subscribers_sub_count
DESC
LIMIT 20
OFFSET 1;
此查询的结果是。。。
| publisher_id | subscriber_id | username | user_photo | subscribers_sub_count |
| 88 | 80 | GERPAL1 | pic.png | 3 |
| 84 | 80 | PURPle | pic2.png | 2 |
| 87 | 80 | Zeeple | pic3.png | 1 |
如果我使OFFSET=2,即OFFSET 2
,结果是。。。
该查询的结果是
| publisher_id | subscriber_id | username | user_photo | subscribers_sub_count |
| 84 | 80 | PURPle | pic2.png | 2 |
| 87 | 80 | Zeeple | pic3.png | 1 |
即使这些结果应该只在偏移量=1时出现。
如果我使OFFSET=3,即OFFSET 3
,结果是。。。
| publisher_id | subscriber_id | username | user_photo | subscribers_sub_count |
| 87 | 80 | Zeeple | pic3.png | 1 |
即使这些结果应该只在偏移量=1时出现。
你知道如何解决这种奇怪的行为吗?如果没有,你在我的查询中看到任何错误吗?
其他信息。。。
用户表
| id | username | user_photo |
| 80 | supercookie| userpic.png|
| 88 | GERPAL1 | pic.png |
| 84 | PURPle | pic2.png |
| 87 | Zeeple | pic3.png |
订阅者表
| id | publisher_id | subscriber_id |
| 1 | 88 | 80 |
| 2 | 88 | 84 |
| 3 | 88 | 87 |
| 4 | 84 | 80 |
| 5 | 84 | 88 |
| 6 | 87 | 80 |
根据PostgreSQL文档,偏移量表示OFFSET says to skip that many rows before beginning to return rows
。
在查询中,您添加了LIMIT 20 OFFSET 2
,这意味着从现有的20行中跳过2行,并且跳过了前两行,并显示了上一页的重复行。
您应该使用以下公式来计算限额和偏移量:
LIMIT total_record_show OFFSET total_record_show * (page_number - 1)
--- page 1 (total_record_show: 20, page_number: 1)
LIMIT 20 OFFSET 0
--- page 2 (total_record_show: 20, page_number: 2)
LIMIT 20 OFFSET 20
--- page 3 (total_record_show: 20, page_number: 3)
LIMIT 20 OFFSET 40