T-SQL查询问题：按区域获取对应最小/最大生均支出的区县名称

作为SQL新手，我尝试按区域汇总各区县的生均支出最小值、最大值，以及对应的区县名称，但目前区县名称总是重复第一个区域的结果，求帮忙修正！

原始数据 #

LA Code Local_Authority Region Spend per pupil ---------------------------------------------------------------- 831 Derby East Midlands 4370 830 Derbyshire East Midlands 4600 822 Bedford East of England 4694 873 Cambridgeshire East of England 4455 301 Barking & Dagenham London 5377 302 Barnet London 4965 330 Birmingham West Midlands 5483 331 Coventry West Midlands 4970

期望的汇总结果 #

Region Min_LA_Name Min_Amount Max_LA_Name Max_Amount --------------------------------------------------------------------------- East Midlands Derby 4370 Derbyshire 4600 East of England Cambridgeshire 4455 Bedford 4694 London Barnet 4965 Barking & Dagenham 5377 West Midlands Coventry 4970 Birmingham 5483

当前使用的SQL代码 #

SELECT Region, (SELECT Local_Authority FROM LASpendPerPupil_df GROUP BY REGION HAVING MIN("Spend per pupil")) AS Min_LA_Name, MIN("Spend per pupil") AS Min_Amount, (SELECT Local_Authority FROM LASpendPerPupil_df GROUP BY REGION HAVING MAX("Spend per pupil")) AS Max_LA_Name, MAX("Spend per pupil") AS Max_Amount FROM LASpendPerPupil_df GROUP BY REGION;

错误的输出结果 #

Region Min_LA_Name Min_Amount Max_LA_Name Max_Amount --------------------------------------------------------------------------- East Midlands Derby 4370 Derbyshire 4600 East of England Derby 4455 Derbyshire 4694 London Derby 4965 Derbyshire 5377 West Midlands Derby 4970 Derbyshire 4970

你的问题出在子查询没有和外部查询的区域做关联，导致子查询每次都返回整个表按区域分组后的第一条结果，而不是当前行对应区域的正确区县。这里给你两种修正方案：

方案1：使用窗口函数（推荐，可读性更强） #

用窗口函数给每个区域内的记录按支出排序，然后筛选出最小和最大支出的那条记录：

WITH RankedSpends AS (
    SELECT 
        Region,
        Local_Authority,
        "Spend per pupil" AS Amount,
        -- 按区域分组，支出升序排名，1就是最小的那条
        ROW_NUMBER() OVER (PARTITION BY Region ORDER BY "Spend per pupil" ASC) AS MinRank,
        -- 按区域分组，支出降序排名，1就是最大的那条
        ROW_NUMBER() OVER (PARTITION BY Region ORDER BY "Spend per pupil" DESC) AS MaxRank
    FROM LASpendPerPupil_df
)
SELECT
    r_min.Region,
    r_min.Local_Authority AS Min_LA_Name,
    r_min.Amount AS Min_Amount,
    r_max.Local_Authority AS Max_LA_Name,
    r_max.Amount AS Max_Amount
FROM RankedSpends r_min
-- 关联同一个CTE，取对应区域的最大支出记录
JOIN RankedSpends r_max ON r_min.Region = r_max.Region
WHERE r_min.MinRank = 1 AND r_max.MaxRank = 1;

方案2：修正关联子查询 #

给子查询加上区域匹配条件，让它只针对当前行的区域查找对应区县：

SELECT 
    Region,
    -- 子查询限定当前区域，按支出升序取第一条
    (SELECT Local_Authority 
     FROM LASpendPerPupil_df sub_min 
     WHERE sub_min.Region = main.Region 
     ORDER BY "Spend per pupil" ASC 
     LIMIT 1) AS Min_LA_Name,
    MIN("Spend per pupil") AS Min_Amount,
    -- 子查询限定当前区域，按支出降序取第一条
    (SELECT Local_Authority 
     FROM LASpendPerPupil_df sub_max 
     WHERE sub_max.Region = main.Region 
     ORDER BY "Spend per pupil" DESC 
     LIMIT 1) AS Max_LA_Name,
    MAX("Spend per pupil") AS Max_Amount
FROM LASpendPerPupil_df main
GROUP BY Region;

为什么原来的代码出错？ #

你原来的子查询(SELECT Local_Authority FROM LASpendPerPupil_df GROUP BY REGION HAVING MIN("Spend per pupil"))没有和外部的main表关联，SQL会执行这个子查询一次，返回所有区域分组后的结果（通常是第一条区域的区县），然后把这个结果重复填充到所有行里，所以才会出现所有区域的区县名称都重复第一个区域的情况。

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