如何查询指定ID之前的X条记录（无需反转结果）

问题场景 #

我有张表，id是int类型，已经排好序但不连续（比如值是1,5,10,11,12,13这类）。查ID 11之后的10条记录很简单：

select * from myTable where id >= 11 limit 10

但查ID 11之前的10条记录时，用这条语句得到的结果是倒序的：

select * from myTable where id < 11 order by id desc limit 10

虽然可以在应用层反转列表调整顺序，但想知道有没有办法直接在SQL里拿到正序的结果？

可行的SQL写法 #

1. 子查询嵌套反转 #

最直接的方式就是先倒序取到目标数据，再在外层把结果正序排列，全程在数据库里完成，不用在代码里处理：

select * from (
    select * from myTable where id < 11 order by id desc limit 10
) as temp order by id asc;

内层子查询先筛出id小于11的记录，倒序取前10条（也就是离11最近的10条），外层再把这部分数据按id正序排，直接得到你要的顺序。

2. 窗口函数方案（适合MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

用ROW_NUMBER()给符合条件的记录按id倒序编号，然后取前10条再正序输出：

select * from (
    select *, ROW_NUMBER() over(order by id desc) as rn
    from myTable where id < 11
) as temp where rn <= 10 order by id asc;

或者换个思路，先按正序给所有符合条件的记录编号，然后取最后10条：

select * from (
    select *, ROW_NUMBER() over(order by id asc) as rn
    from myTable where id < 11
) as temp 
where rn > (select count(*) from myTable where id < 11) - 10 
order by id asc;

这种写法的好处是，当id小于11的记录不足10条时，会自动返回所有符合条件的记录，不用额外处理边界情况。

3. 利用LIMIT偏移量（局限性较大） #

如果你能提前知道id小于11的记录总数，比如总数是N，那就可以用LIMIT N-10, 10直接取正序的最后10条。但这种方法需要先查一次总数，而且当数据有变化时偏移量就失效了，不如前两种灵活：

-- 先查总数
select count(*) from myTable where id < 11;
-- 假设总数>=10，执行下面的查询（不同数据库语法可能有差异）
select * from myTable where id < 11 order by id asc limit (select count(*)-10 from myTable where id <11), 10;

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