PostgreSQL多表关联分页（LIMIT与OFFSET）中获取唯一记录总数的实现方法

这个问题我之前也碰到过，核心原因是窗口函数COUNT(*) OVER()的执行时机在DISTINCT之前——JOIN操作会生成多条关联记录（比如你的场景里每个学生和对应教师的关联都会产生一行），窗口函数会先统计这些未去重的行数，而DISTINCT是在最后才对结果去重，所以得到了300而非预期的100。

下面给你几个简便的解决方法，按推荐优先级排序：

方法一：用EXISTS替代JOIN（最优解） #

既然你只需要获取关联指定教师的学生信息，不需要教师表的字段，用EXISTS可以直接筛选出符合条件的唯一学生行，完全避免重复记录，窗口函数的COUNT(*)就能正确统计总数：

SELECT s.*, COUNT(*) OVER() AS total_count
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM student_teacher st
    WHERE st.studentId = s.id AND st.teacherId = 2
)
LIMIT 10 OFFSET 0;

这种方法性能最优，因为EXISTS只要找到匹配的关联记录就会停止检索，不会生成冗余的JOIN结果，代码也更简洁。

方法二：窗口函数结合DISTINCT（简洁语法） #

如果你需要保留JOIN的写法（比如要用到教师表的字段），可以在窗口函数里直接统计唯一的学生ID，PostgreSQL 9.4及以上版本支持这种语法：

SELECT DISTINCT s.*, COUNT(DISTINCT s.id) OVER() AS total_count
FROM student s
JOIN student_teacher st ON st.studentId = s.id
JOIN teacher t ON st.teacherId = t.id
WHERE t.id = 2
LIMIT 10 OFFSET 0;

这里COUNT(DISTINCT s.id) OVER()会忽略重复的学生ID，直接返回符合条件的唯一学生总数100。

方法三：子查询预计算总数（兼容性最好） #

如果你的PostgreSQL版本较低（不支持窗口函数里的DISTINCT），可以用子查询先筛选出所有符合条件的唯一学生，再分别计算总数和分页结果：

SELECT s.*, (SELECT COUNT(*) FROM (
    SELECT DISTINCT student.id
    FROM student
    JOIN student_teacher st ON st.studentId = student.id
    WHERE st.teacherId = 2
) AS filtered_students) AS total_count
FROM (
    SELECT DISTINCT student.*
    FROM student
    JOIN student_teacher st ON st.studentId = student.id
    WHERE st.teacherId = 2
    LIMIT 10 OFFSET 0
) AS s;

内层的filtered_students子查询负责统计唯一学生总数，外层子查询处理分页，兼容性拉满但写法稍显繁琐。

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