如何获取SQL查询的第N条结果？以获取第二条结果为例

针对你的场景——从Backup表中筛选id_device = 5的记录并按id降序排列，取第二条结果的nb_cut（即123），不同SQL方言的实现方式如下：

MySQL/MariaDB #

使用LIMIT + OFFSET语法，跳过前1条后取1条：

SELECT nb_cut 
FROM Backup 
WHERE id_device = 5 
ORDER BY id DESC 
LIMIT 1 OFFSET 1;

• OFFSET 1：跳过排序后的第一条记录
• LIMIT 1：只返回接下来的1条记录

PostgreSQL #

支持LIMIT/OFFSET和标准SQL的FETCH语法：

-- 方式1：LIMIT/OFFSET
SELECT nb_cut 
FROM Backup 
WHERE id_device = 5 
ORDER BY id DESC 
OFFSET 1 LIMIT 1;

-- 方式2：FETCH NEXT
SELECT nb_cut 
FROM Backup 
WHERE id_device = 5 
ORDER BY id DESC 
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

SQL Server #

• 新版本（2012+）用OFFSET...FETCH：

SELECT nb_cut 
FROM Backup 
WHERE id_device = 5 
ORDER BY id DESC 
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

• 旧版本用嵌套TOP查询：

SELECT TOP 1 nb_cut 
FROM (
    SELECT TOP 2 nb_cut 
    FROM Backup 
    WHERE id_device = 5 
    ORDER BY id DESC
) AS sub 
ORDER BY id ASC;

先取前2条，再倒序取第一条（即原排序的第二条）。

Oracle #

• 用ROWNUM嵌套查询：

SELECT nb_cut 
FROM (
    SELECT nb_cut, ROWNUM AS rn 
    FROM (
        SELECT nb_cut 
        FROM Backup 
        WHERE id_device = 5 
        ORDER BY id DESC
    )
) 
WHERE rn = 2;

• 更灵活的窗口函数方式：

SELECT nb_cut 
FROM (
    SELECT nb_cut, 
           ROW_NUMBER() OVER (ORDER BY id DESC) AS rn 
    FROM Backup 
    WHERE id_device = 5
) 
WHERE rn = 2;

通用思路 #

无论哪种数据库，核心逻辑都是：

1. 先按指定规则（ORDER BY id DESC）对结果集排序
2. 跳过前N-1条记录（这里N=2，所以跳过1条）
3. 仅获取第N条记录

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