如何在SQLite3及其他SQL中跳过查询结果的首尾行？

SQLite3 优雅实现 #

使用窗口函数ROW_NUMBER()可以直接给前10条记录分配行号，再筛选掉行号为1（第一条）和最大行号（最后一条）的记录，无需两次倒序/正序排序：

WITH ranked_employees AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn
  FROM employees
  LIMIT 10
)
SELECT * EXCEPT (rn)
FROM ranked_employees
WHERE rn > 1 AND rn < (SELECT MAX(rn) FROM ranked_employees);

如果确定原查询最多返回10条数据，也可以直接指定行号范围，写法更简洁：

WITH ranked_employees AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn
  FROM employees
  LIMIT 10
)
SELECT * EXCEPT (rn)
FROM ranked_employees
WHERE rn BETWEEN 2 AND 9;

这种方法的优势：

• 仅需一次排序操作，性能比两次倒序/正序的CTE更优
• 自动适配原查询的实际返回行数：如果employees表不足10条数据，会自动跳过第一条和最后一条（比如返回5条时，取第2-4条），无需手动判断结果集大小

其他SQL方言实现 #

大部分现代SQL方言（PostgreSQL、MySQL 8.0+、SQL Server等）都支持窗口函数，核心逻辑一致，仅需调整列排除的语法：

PostgreSQL / MySQL 8.0+ #

WITH ranked_employees AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn
  FROM employees
  LIMIT 10
)
SELECT id, name, department, ... -- 手动列出除rn外的所有字段
FROM ranked_employees
WHERE rn BETWEEN 2 AND 9;

SQL Server #

WITH ranked_employees AS (
  SELECT TOP 10 *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn
  FROM employees
)
SELECT id, name, department, ... -- 手动列出除rn外的所有字段
FROM ranked_employees
WHERE rn BETWEEN 2 AND 9;

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