数据库自连接如何分页

数据库自连接（self join）本质上就是一个表和自己 join，分页思路和普通查询一样，只是先 join，再分页。下面按常见数据库分别说明，并给出注意事项。

一、基本思路（通用）

1. 先写自连接查询
2. 明确排序字段（分页必须有序）
3. 再在外部做分页（LIMIT / OFFSET 或窗口函数）

示例表：

emp (
  id,
  name,
  manager_id
)

自连接：查员工及其经理

SELECT
  e.id,
  e.name AS emp_name,
  m.name AS manager_name
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id
ORDER BY e.id;

二、MySQL / PostgreSQL（LIMIT + OFFSET）

第 1 页（每页 10 条）

SELECT
  e.id,
  e.name AS emp_name,
  m.name AS manager_name
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id
ORDER BY e.id
LIMIT 10 OFFSET 0;

第 2 页

LIMIT 10 OFFSET 10;

✅ 简单直观
⚠️ 深分页（OFFSET 很大）性能差

三、SQL Server（OFFSET FETCH）

SELECT
  e.id,
  e.name AS emp_name,
  m.name AS manager_name
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id
ORDER BY e.id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

四、Oracle（ROWNUM / ROW_NUMBER）

推荐写法（12c+）

SELECT *
FROM (
  SELECT
    e.id,
    e.name AS emp_name,
    m.name AS manager_name,
    ROW_NUMBER() OVER (ORDER BY e.id) rn
  FROM emp e
  LEFT JOIN emp m ON e.manager_id = m.id
)
WHERE rn BETWEEN 11 AND 20;

五、高性能分页（游标分页，推荐）

避免 OFFSET 深分页问题。

SELECT
  e.id,
  e.name AS emp_name,
  m.name AS manager_name
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id
WHERE e.id > :last_id
ORDER BY e.id
LIMIT 10;

✅ 性能稳定
❌ 不支持“跳页”

六、自连接分页常见坑

1️⃣ 排序字段不唯一 → 分页错位

✅ 用 唯一键 + 排序字段

ORDER BY e.id

2️⃣ JOIN 后行数变化

• 自连接不会减少行数（除非 INNER JOIN 过滤）
• 分页基于 JOIN 后的结果集

3️⃣ 聚合 + 自连接

先聚合，再分页：

WITH t AS (
  SELECT e.manager_id, COUNT(*) cnt
  FROM emp e
  GROUP BY e.manager_id
)
SELECT * FROM t
ORDER BY cnt DESC
LIMIT 10;

七、总结

如果你有具体数据库 + SQL，我可以直接帮你改写分页版本。