编写SQL查询：筛选总降雨量高于全城市平均值的州及其总量

要实现需求，我们可以通过嵌套子查询完成计算与筛选：

SELECT state, total_rain
FROM (
    -- 第一步：计算每个州的总降雨量
    SELECT state, SUM(rainfall) AS total_rain
    FROM Rainfall
    GROUP BY state
) AS state_rain_totals
-- 第二步：筛选出总降雨量高于所有州平均总降雨量的记录
WHERE total_rain > (
    SELECT AVG(total_rain)
    FROM (
        SELECT SUM(rainfall) AS total_rain
        FROM Rainfall
        GROUP BY state
    ) AS avg_calculation
);

语句说明 #

• 最内层子查询avg_calculation先按州分组计算总降雨量，再基于这些结果算出所有州的平均总降雨量
• 中间层子查询state_rain_totals计算每个州的总降雨量
• 外层查询将州的总降雨量与平均值对比，输出符合条件的州和对应总降雨量

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