如何无需Left Join，通过单查询获取同一表的聚合统计结果？

你的需求是查询每个学生的每一条分数记录，同时附上该学生所有不同分数的数量，原写法通过LEFT JOIN子查询实现，确实可以优化成单条查询，用窗口函数就能搞定，不需要额外的子查询和JOIN操作。

优化后的SQL语句如下：

SELECT 
    student_id,
    marks,
    COUNT(DISTINCT marks) OVER (PARTITION BY student_id) AS d_marks
FROM student_marks;

为什么这样更优？ #

• 原写法需要对student_marks表扫描两次（一次外层查询，一次子查询分组），而窗口函数只需要一次扫描，性能更优
• 代码逻辑更直观，不需要额外的JOIN关联，减少冗余语法
• 窗口函数的PARTITION BY student_id会按学生ID分组计算聚合值，COUNT(DISTINCT marks)统计每个学生的不同分数数量，结果会自动匹配到每一行对应的学生记录上

注意：部分老版本数据库可能对窗口函数中的COUNT(DISTINCT)支持有限（比如MySQL 8.0之前的版本），如果你的数据库版本不支持，可以用替代方案：先通过子查询计算每个学生的不同分数数，再用窗口函数关联——不过这种情况还是建议优先升级数据库版本，或者用原写法的等价优化，但多数现代关系型数据库（PostgreSQL、MySQL 8.0+、SQL Server等）都支持窗口函数中的COUNT(DISTINCT)。

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