可以使用分析函数把总记录数检索出来,比如 标准分页SQL:SELECT COUNT(*) FROM employees; COUNT(*) ---------- 61SELECT n.employee_id FROM (SELECT m.employee_id, ROWNUM rn FROM (SELECT t.employee_id FROM employees t ORDER BY t.employee_id) m WHERE ROWNUM <= 4) n WHERE n.rn >= 2EMPLOYEE_ID ----------- 2 3 4分析函数:SELECT n.employee_id, n.total_cnt FROM (SELECT m.employee_id, m.total_cnt, ROWNUM rn FROM (SELECT t.employee_id, COUNT(*) OVER() total_cnt FROM employees t ORDER BY t.employee_id) m WHERE ROWNUM <= 4) n WHERE n.rn >= 2EMPLOYEE_ID TOTAL_CNT ----------- ---------- 2 61 3 61 4 61然后根据第一行TOTAL_CNT获取记录数就可以了。
标准分页SQL:SELECT COUNT(*) FROM employees; COUNT(*)
----------
61SELECT n.employee_id
FROM (SELECT m.employee_id,
ROWNUM rn
FROM (SELECT t.employee_id
FROM employees t
ORDER BY t.employee_id) m
WHERE ROWNUM <= 4) n
WHERE n.rn >= 2EMPLOYEE_ID
-----------
2
3
4分析函数:SELECT n.employee_id,
n.total_cnt
FROM (SELECT m.employee_id,
m.total_cnt,
ROWNUM rn
FROM (SELECT t.employee_id,
COUNT(*) OVER() total_cnt
FROM employees t
ORDER BY t.employee_id) m
WHERE ROWNUM <= 4) n
WHERE n.rn >= 2EMPLOYEE_ID TOTAL_CNT
----------- ----------
2 61
3 61
4 61然后根据第一行TOTAL_CNT获取记录数就可以了。