PostgreSQL使用DISTINCT时COUNT(*) OVER()计数不正确如何解决

问题原因 #

你现有写法中COUNT(*) OVER()窗口函数的计算优先级高于DISTINCT，统计的是多表关联后的原始9行未去重结果，因此和最终去重后的3行数据数量不一致。

解决方案 #

方法1：子查询嵌套（逻辑最直观）

先在内层查询拿到去重后的学生列表，在外层统计总条数即可：

SELECT *, COUNT(*) OVER() AS "total_count"
FROM (
    SELECT DISTINCT student.* 
    FROM student
    JOIN student_teacher ON student_teacher.studentId = student.id
    JOIN teacher ON student_teacher.teacherId = teacher.id
    JOIN teacher_classroom ON teacher_classroom.teacherId = teacher.id
    JOIN classroom ON classroom.id = teacher_classroom.classroomId 
) AS distinct_students
LIMIT 10 OFFSET 0;

方法2：直接统计唯一ID（写法更简洁）

不需要嵌套子查询，直接用窗口函数统计去重后的学生ID总数：

SELECT DISTINCT student.*, COUNT(DISTINCT student.id) OVER() AS "total_count"
FROM student
JOIN student_teacher ON student_teacher.studentId = student.id
JOIN teacher ON student_teacher.teacherId = teacher.id
JOIN teacher_classroom ON teacher_classroom.teacherId = teacher.id
JOIN classroom ON classroom.id = teacher_classroom.classroomId 
LIMIT 10 OFFSET 0;

两种方案返回的total_count都是正确的去重后行数3。如果需要分页结果准确，建议优先使用第一种方法，确保LIMIT和OFFSET作用于已经去重的结果集。

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