对于大型数据集, offset
基于指针的分页变得很慢,因此一种更快的方法是使用基于指针的分页。基本上是一个锚定点,数据库知道从该点开始查找结果。有鉴于此,我面临的问题是:
我有一张table tv_watchers
自动递增 id
, mins_watching_tv
,和 user_id
(下面总共有20排小提琴)。在这个例子中 user_id
会是一样的 1
,所以不用担心。我们想按看电视的分钟数从高到低排序。
使用此查询可以轻松完成此操作:
SELECT * FROM tv_watchers
ORDER BY mins_watching_tv DESC, id ASC
这将返回按id排序的20个字段的正确顺序:
2, 17, 1, 16, 15, 5, 6, 7, 8, 9, 10, 11, 12, 13, 20, 3, 4, 14, 19, 18
问题是我们想把它分成5个部分(我们称之为批处理),因为我们想按上面的顺序返回5个结果。我们通过检索前6个结果,将前5个结果返回给用户,并使用第6个结果(如果存在)作为光标(定位点)从中获取下一批结果:这将正确返回第一批:
-- (Batch 1) 2, 17, 1, 16, 15, 5
SELECT * FROM tv_watchers
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6
第六项是身份证 5
它有一个 mins_watching_tv
的 60
,因此,由于这是光标,我们使用它来获得下一个6,如下所示:
-- (Batch 2) 5, 6, 7, 8, 9, 10
SELECT * FROM tv_watchers
WHERE mins_watching_tv <= 60 OR id=5
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6
第六项是身份证 10
它也有一个 mins_watching_tv
的 60
,因此,由于这是光标,我们使用它来获得下一个6,如下所示:
-- (Batch 3 should be) 10, 11, 12, 13, 20, 3
-- (Batch 3 returns incorrectly) 5, 6, 7, 8, 9, 10
SELECT * FROM tv_watchers
WHERE mins_watching_tv <= 60 OR id=10
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6
但是问题是返回的结果是不正确的,它返回了上面评论中看到的不正确的batch3id。我相信这和 WHERE
部分,它似乎拿起了 mins_watching_tv <= 60
部分,但是 id=10
第二部分是让数据库知道从60分钟和id 10的锚定点获取结果,但这并不能正常工作。
最终批次结果应如下所示:
-- (Batch 4) 3, 4, 14, 19, 18
我在这里设置了一个sql fiddle来显示这个问题。我们如何修复查询,使其符合 mins_watching_tv
结合 id
批量返回正确的结果?
2条答案
按热度按时间pbgvytdp1#
像以前一样选择前6个,但不在列表中选择任何内容
WHERE
.持续时间
@duration
身份证呢@id
上一步结果的最后一行,并将它们放入WHERE
喜欢重复2。直到到达终点。
说明:
如果
mins_watching_tv < @duration
我们可以确定,相应的行不在我们之前的结果中mins_watching_tv
小于最小值@duration
从我们之前的结果来看ORDER BY mins_watching_tv DESC
.如果
mins_watching_tv = @duration
我们还不知道我们是否已经吵架了。但是我们还做了一个ORDER BY id ASC
,我们知道我们已经有了相同的行mins_watching_tv
id小于或等于当前最大值@id
(根据mins_watching_tv
). 所以我们只想要那些id > @id
或者,我们还想重复上一个结果的最后一行,id = @id
. 简言之,那是id >= @id
.当我们想要这两个集合的并集时,我们必须分离上面的 predicate ,所以使用
OR
. 我们得到(括号只是为了清楚起见,不需要):这是小提琴。
biswetbf2#
我只略读了一下,但我认为你只需要调整你的条件(例如)
mins_watching_tv < 60 OR (mins_watching_tv = 60 AND id>=5)