有没有一种方法可以在订单列表中的特定ID之后选择结果? [英] Is there a way to select results after a certain id in an order list?

问题描述

我正在尝试基于Postgres数据库中的数据实现基于游标的分页列表.

I'm trying to implement a cursor-based paginating list based off of data from a Postgres database.

作为一个例子，假设我有一个包含以下各列的表:

As an example, say I have a table with the following columns:

id | firstname | lastname

我想对这些数据进行分页，如果我只想按ID对其进行排序，这将非常简单，但是在我的情况下，我希望该选项按姓氏进行排序，并且可以保证有多个人相同的姓氏.

I want to paginate this data, which would be pretty simple if I only ever wanted to sort it by the id, but in my case, I want the option to sort by last name, and there's guaranteed to be multiple people with the same last name.

如果我有如下选择语句:

If I have a select statement like follows:

SELECT * FROM people
ORDER BY lastname ASC;

在这种情况下，我可以使编码的游标包含有关lastname的信息，因此我可以从上次中断的地方继续学习，但是由于会有多个具有相同姓氏的用户，所以这会带来麻烦.在SQL中，是否有一种方法只能在有序列表中的某个ID后面的某个ID处获取结果，而不是对结果进行排序的列?

In the case, I could make my encoded cursor contain information about the lastname so I could pick up where I left off, but since there will be multiple users with the same last name, this will be buggy. Is there a way in SQL to only get the results after a certain id in an ordered list where it is not the column by which the results are sorted?

select语句的示例结果:

Example results from the select statement:

1 | John  | Doe
4 | John  | Price
2 | Joe   | White
6 | Jim   | White
3 | Sam   | White
5 | Sally | Young

如果我希望页面大小为3，则无法添加WHERE lastname <= :lastname，因为列表上有重复的数据，因为在该调用期间它将返回ID 2、6和3.就我而言，如果可以在查询中添加类似于AFTER id = 6的内容，它将跳过所有内容，直到在有序列表中找到id.

If I wanted a page size of 3, I couldn't add WHERE lastname <= :lastname as I'd have duplicate data on the list since it would return ids 2, 6, and 3 during that call. In my case, it'd be helpful if I could add to my query something similar to AFTER id = 6 where it could skip everything until it finds that id in the ordered list.

推荐答案

是.如果我理解正确:

select t.*
from t
where (lastname, id) > (select t2.lastname, t2.id
                        from t t2
                        where t2.id = ?
                       )
order by t.lastname;

我想我会在组合中添加firstname，但这是相同的想法.

I think I would add firstname into the mix, but it is the same idea.

这篇关于有没有一种方法可以在订单列表中的特定ID之后选择结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！