如何从Crude_Rate提取最大值？SQL分组后Disease与数值不匹配

Hey Allen, let's break down why your current query isn't linking the correct Disease to the maximum Crude_Rate per state, and fix it with reliable solutions.

What's Wrong with the Original Query? #

Your current query has two key issues:

1. Incorrect GROUP BY logic: When you GROUP BY M.State but include M.Disease in the SELECT clause, most SQL databases (unless in a non-standard mode) will return an arbitrary Disease value from the group—not the one associated with the maximum Crude_Rate. That's why your Disease and max rate don't match.
2. Unnecessary NOT EXISTS: Your subquery to exclude "Total" is overcomplicating things. You can directly filter out Disease = 'Total' with a simple WHERE condition.

Solution 1: Use Window Functions (Modern, Clean Approach) #

Window functions like ROW_NUMBER() or RANK() let you rank records within each state by Crude_Rate, then pick the top result. This is the most straightforward method for modern SQL databases (PostgreSQL, MySQL 8+, SQL Server, etc.):

SELECT Year, State, Disease, Crude_Rate
FROM (
    SELECT 
        Year,
        State,
        Disease,
        Crude_Rate,
        -- Assign a rank to each record in the state, sorted by Crude_Rate descending
        ROW_NUMBER() OVER (PARTITION BY State ORDER BY Crude_Rate DESC) AS rate_rank
    FROM MultipleDiseases
    WHERE Year = 2000 AND Disease != 'Total'
) AS ranked_records
-- Pick only the top-ranked record per state
WHERE rate_rank = 1;

• Note: Use RANK() instead of ROW_NUMBER() if you want to return all Diseases that share the maximum Crude_Rate in a state (instead of just one).

Solution 2: Use a Subquery to Find Max Rates First #

If you're working with an older SQL version that doesn't support window functions, this approach works by first calculating the maximum Crude_Rate per state, then joining back to the original table to get the matching Disease:

SELECT m.Year, m.State, m.Disease, m.Crude_Rate
FROM MultipleDiseases m
INNER JOIN (
    -- Get the max Crude_Rate for each state in 2000 (excluding Total)
    SELECT State, MAX(Crude_Rate) AS max_crude_rate
    FROM MultipleDiseases
    WHERE Year = 2000 AND Disease != 'Total'
    GROUP BY State
) AS state_max_rates
ON m.State = state_max_rates.State 
AND m.Crude_Rate = state_max_rates.max_crude_rate
WHERE m.Year = 2000 AND m.Disease != 'Total';

• Note: This will return multiple rows for a state if multiple Diseases have the same maximum Crude_Rate.

Why These Solutions Work #

Both methods ensure that we first identify the maximum Crude_Rate per state, then explicitly fetch the Disease associated with that rate—instead of letting the database pick a random Disease from the group.

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