SQL新手技术咨询：如何获取数据表中各年份列最大值对应的州

嘿，作为SQL新手碰到这种需求完全正常，我来帮你搞定这个问题～

首先得先解决两个小问题：你的年份列是varchar类型，而且还有NA值，直接转int会报错，所以第一步要把NA转换成NULL，再转成整数类型，这样才能正确计算最大值。

接下来，我们有两种常用方法来实现你的需求：

方法一：用子查询+UNION ALL合并结果 #

这种方法比较直观，分别找出每个年份的最大值，再关联表找到对应的州，最后把三个年份的结果合并起来：

SELECT 
    'Year_06' AS year_column,
    State AS top_state,
    Year_06 AS max_value
FROM your_table
WHERE NULLIF(Year_06, 'NA')::int = (SELECT MAX(NULLIF(Year_06, 'NA')::int) FROM your_table)
UNION ALL
SELECT 
    'Year_07' AS year_column,
    State AS top_state,
    Year_07 AS max_value
FROM your_table
WHERE NULLIF(Year_07, 'NA')::int = (SELECT MAX(NULLIF(Year_07, 'NA')::int) FROM your_table)
UNION ALL
SELECT 
    'Year_08' AS year_column,
    State AS top_state,
    Year_08 AS max_value
FROM your_table
WHERE NULLIF(Year_08, 'NA')::int = (SELECT MAX(NULLIF(Year_08, 'NA')::int) FROM your_table);

代码说明： #

• NULLIF(Year_06, 'NA')：把NA转换成NULL，避免转int时出错
• 每个子查询先算出对应年份的最大值，再筛选出表中值等于这个最大值的行
• UNION ALL把三个年份的结果合并成一个结果集，year_column列用来区分是哪一年的数据

方法二：用窗口函数（更灵活） #

如果以后年份列变多，这种方法更易维护。先把宽表转成窄表（行转列），再用窗口函数排序取每个年份的最大值对应的州：

WITH unpivoted_data AS (
    SELECT 
        State,
        unnest(array['Year_06', 'Year_07', 'Year_08']) AS year_column,
        unnest(array[NULLIF(Year_06, 'NA')::int, NULLIF(Year_07, 'NA')::int, NULLIF(Year_08, 'NA')::int]) AS year_value
    FROM your_table
),
ranked_data AS (
    SELECT 
        year_column,
        State AS top_state,
        year_value AS max_value,
        ROW_NUMBER() OVER (PARTITION BY year_column ORDER BY year_value DESC NULLS LAST) AS rn
    FROM unpivoted_data
)
SELECT year_column, top_state, max_value
FROM ranked_data
WHERE rn = 1;

代码说明： #

1. unpivoted_data CTE：把原来的列（Year_06/07/08）转成行，每个州对应三条记录（分别对应三个年份）
2. ranked_data CTE：用ROW_NUMBER()按年份分组，按年份值降序排序，NULLS LAST确保NA转成的NULL排在最后，不影响最大值判断
3. 最后筛选出每个年份排名第一的行，就是最大值对应的州

额外提示： #

如果有多个州在同一年份有相同的最大值，ROW_NUMBER()只会返回其中一个。如果要显示所有并列的州，把ROW_NUMBER()换成RANK()或者DENSE_RANK()即可。

记得把代码里的your_table换成你实际的表名哦！

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