查询所有记录中排除Population列的最值列名及对应数值

Got it, let's figure out how to solve this problem. You want to, for each state record, find the column names and their corresponding values for the maximum and minimum values among the age group columns (excluding the Population column), then output the result in the specified format. Here are a few practical approaches depending on your SQL database dialect:

1. Universal SQL Approach (Works in Most Databases) #

This method uses subqueries with VALUES to unpivot the age columns into row pairs, then sorts to pick the max/min column names. We use GREATEST and LEAST to directly get the max/min values:

SELECT
    t.State,
    t.Population,
    -- Get column name of maximum age group value
    (SELECT col_name FROM (
        VALUES
            ('age_below_18', t.age_below_18),
            ('age_18_to_50', t.age_18_to_50),
            ('age_50_above', t.age_50_above)
    ) AS temp(col_name, val) ORDER BY val DESC LIMIT 1) AS Maximum_group,
    -- Get column name of minimum age group value
    (SELECT col_name FROM (
        VALUES
            ('age_below_18', t.age_below_18),
            ('age_18_to_50', t.age_18_to_50),
            ('age_50_above', t.age_50_above)
    ) AS temp(col_name, val) ORDER BY val ASC LIMIT 1) AS Minimum_group,
    -- Get the maximum value directly
    GREATEST(t.age_below_18, t.age_18_to_50, t.age_50_above) AS Max_value,
    -- Get the minimum value directly
    LEAST(t.age_below_18, t.age_18_to_50, t.age_50_above) AS Min_value
FROM your_table t;

2. PostgreSQL-Specific Approach (Using LATERAL Joins) #

PostgreSQL's LATERAL join lets us create a temporary per-row dataset, making the query cleaner and avoiding repeated subqueries:

SELECT
    t.State,
    t.Population,
    max_group.col_name AS Maximum_group,
    min_group.col_name AS Minimum_group,
    max_group.val AS Max_value,
    min_group.val AS Min_value
FROM your_table t
-- Join to get the max age group row
LEFT JOIN LATERAL (
    SELECT col_name, val
    FROM (
        VALUES
            ('age_below_18', t.age_below_18),
            ('age_18_to_50', t.age_18_to_50),
            ('age_50_above', t.age_50_above)
    ) AS temp(col_name, val)
    ORDER BY val DESC LIMIT 1
) max_group ON TRUE
-- Join to get the min age group row
LEFT JOIN LATERAL (
    SELECT col_name, val
    FROM (
        VALUES
            ('age_below_18', t.age_below_18),
            ('age_18_to_50', t.age_18_to_50),
            ('age_50_above', t.age_50_above)
    ) AS temp(col_name, val)
    ORDER BY val ASC LIMIT 1
) min_group ON TRUE;

3. MySQL 8.0+ Approach (Using JSON_TABLE) #

MySQL 8.0 introduced JSON_TABLE, which we can use to unpivot the age columns into a row set:

SELECT
    t.State,
    t.Population,
    -- Get max age group column name
    (SELECT col_name FROM JSON_TABLE(
        JSON_ARRAY(
            JSON_OBJECT('col_name', 'age_below_18', 'val', t.age_below_18),
            JSON_OBJECT('col_name', 'age_18_to_50', 'val', t.age_18_to_50),
            JSON_OBJECT('col_name', 'age_50_above', 'val', t.age_50_above)
        ),
        '$[*]' COLUMNS(
            col_name VARCHAR(20) PATH '$.col_name',
            val INT PATH '$.val'
        )
    ) AS temp ORDER BY val DESC LIMIT 1) AS Maximum_group,
    -- Get min age group column name
    (SELECT col_name FROM JSON_TABLE(
        JSON_ARRAY(
            JSON_OBJECT('col_name', 'age_below_18', 'val', t.age_below_18),
            JSON_OBJECT('col_name', 'age_18_to_50', 'val', t.age_18_to_50),
            JSON_OBJECT('col_name', 'age_50_above', 'val', t.age_50_above)
        ),
        '$[*]' COLUMNS(
            col_name VARCHAR(20) PATH '$.col_name',
            val INT PATH '$.val'
        )
    ) AS temp ORDER BY val ASC LIMIT 1) AS Minimum_group,
    -- Direct max value
    GREATEST(t.age_below_18, t.age_18_to_50, t.age_50_above) AS Max_value,
    -- Direct min value
    LEAST(t.age_below_18, t.age_18_to_50, t.age_50_above) AS Min_value
FROM your_table t;

Output Verification #

All these queries will produce exactly the output you're expecting:

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