SQL如何在查询中引用查询范围外的聚合函数计算结果

原有查询的问题 #

WHERE子句会先过滤出所有GenreShort = 1的短片记录，后续计算的AVG(MovieLength)仅统计了短片的平均片长，且未搭配GROUP BY直接用HAVING对比字段与聚合结果的写法也不符合标准SQL执行规则。

解决方案 #

方案1：标量子查询实现（全数据库兼容） #

通过独立子查询计算全表所有电影的平均片长，直接放到WHERE条件中对比即可，不受外层过滤逻辑影响：

SELECT MovieTitle, MovieLength
FROM MOVIES
WHERE GenreShort = 1
  AND MovieLength > (SELECT AVG(MovieLength) FROM MOVIES);

方案2：窗口函数实现（适配支持窗口函数的数据库） #

如果需要在结果中同步展示全表平均片长做参照，可以用窗口函数提前计算全表平均值后再过滤：

SELECT MovieTitle, MovieLength
FROM (
    SELECT 
        MovieTitle, 
        MovieLength,
        GenreShort,
        AVG(MovieLength) OVER () AS all_avg_length
    FROM MOVIES
) t
WHERE GenreShort = 1 
  AND MovieLength > all_avg_length;

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