如何从多表聚合分组查询返回多列（适配MySQL 5.7）

Got it, let's tackle this MySQL 5.7 GROUP BY problem you're hitting. The root cause is MySQL 5.7 enabling ONLY_FULL_GROUP_BY by default—this enforces strict SQL standards, requiring every non-aggregated column in your SELECT to be included in the GROUP BY clause. Your original query only grouped by eventID, which worked pre-5.7 because MySQL would silently pick arbitrary values for other columns, but adding those columns to GROUP BY splits your results into way too many groups (since you're grouping by every unique combination of athlete, date, etc.).

Solution 1: MySQL 5.7 Compatible (Subquery Approach) #

This method first calculates the best result for each event, then joins back to the original tables to fetch the corresponding athlete and event details:

SELECT
    e.eventName AS Event,
    -- Format result with unit for throws
    CONCAT(
        eb.best_result,
        CASE WHEN e.eventName LIKE '%Throw' THEN 'm' ELSE '' END
    ) AS Result,
    CONCAT(a.nameFirst, ' ', a.nameLast) AS `Athlete Name`
FROM (
    -- Step 1: Get the best result for each event
    SELECT
        r.eventID,
        e.eventName,
        CASE
            -- For track events (like 100m/200m), get the smallest time
            WHEN e.eventName LIKE '%m' THEN MIN(r.time)
            -- For throw/jump events, get the largest distance/height
            WHEN e.eventName LIKE '%Throw' OR e.eventName LIKE '%Jump' THEN MAX(r.distHeight)
        END AS best_result
    FROM results r
    INNER JOIN events e ON r.eventID = e.eventID
    WHERE
        r.ageGroup = 'MS'
        AND YEAR(r.date) = 2008 -- Note: Your sample data uses 2008, adjust if needed
        AND r.wind < 2.1
        AND r.wind != 'nwr'
    GROUP BY r.eventID, e.eventName
) eb
-- Step 2: Join back to results to find the exact record with the best result
INNER JOIN results r
    ON eb.eventID = r.eventID
    AND (
        (eb.best_result = r.time AND eb.eventName LIKE '%m')
        OR (eb.best_result = r.distHeight AND (eb.eventName LIKE '%Throw' OR eb.eventName LIKE '%Jump'))
    )
INNER JOIN athletes a ON r.athleteID = a.athleteID
INNER JOIN events e ON eb.eventID = e.eventID
WHERE
    r.ageGroup = 'MS'
    AND YEAR(r.date) = 2008
    AND r.wind < 2.1
    AND r.wind != 'nwr'
ORDER BY e.eventID ASC;

Solution 2: MySQL 8.0+ (Window Function Approach - Cleaner) #

If you're able to upgrade to MySQL 8.0 or later, window functions make this much simpler. We'll rank results per event, then pick the top-ranked one:

WITH ranked_results AS (
    SELECT
        e.eventName,
        r.time,
        r.distHeight,
        CONCAT(a.nameFirst, ' ', a.nameLast) AS athlete_name,
        -- Rank results: fastest time first for track, longest distance first for throws
        CASE
            WHEN e.eventName LIKE '%m' THEN
                ROW_NUMBER() OVER (
                    PARTITION BY r.eventID
                    ORDER BY r.time ASC, r.date ASC -- Break ties with earliest date
                )
            WHEN e.eventName LIKE '%Throw' OR e.eventName LIKE '%Jump' THEN
                ROW_NUMBER() OVER (
                    PARTITION BY r.eventID
                    ORDER BY r.distHeight DESC, r.date ASC
                )
        END AS result_rank
    FROM results r
    INNER JOIN athletes a ON r.athleteID = a.athleteID
    INNER JOIN events e ON r.eventID = e.eventID
    WHERE
        r.ageGroup = 'MS'
        AND YEAR(r.date) = 2008
        AND r.wind < 2.1
        AND r.wind != 'nwr'
)
SELECT
    eventName AS Event,
    CONCAT(
        CASE WHEN eventName LIKE '%m' THEN time ELSE distHeight END,
        CASE WHEN eventName LIKE '%Throw' THEN 'm' ELSE '' END
    ) AS Result,
    athlete_name AS `Athlete Name`
FROM ranked_results
WHERE result_rank = 1
ORDER BY eventName ASC;

Key Notes & Optimizations #

• Handling Ties: Both solutions include a tiebreaker (earliest date) to ensure only one result per event if multiple athletes hit the same best mark. Adjust the ORDER BY in the window function or subquery if you need a different tiebreaker.
• Event Type Detection: The LIKE checks for event names are flexible—you can replace them with explicit eventID checks if your event IDs are fixed (e.g., WHEN r.eventID IN (1,2) for track events).
• Performance: Add indexes on results.eventID, results.ageGroup, results.date, and results.wind to speed up the filtering and joining.

Expected Output #

Running either query will give you the clean format you want:

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