如何从多个MySQL表中汇总各racer_nr的total_time_spend总和？

Yes, you can absolutely aggregate the total total_time_spend for each racer_nr across 5+ MySQL tables. The approach depends a bit on whether your tables have consistent structures or not—let’s break down both common scenarios:

Scenario 1: All tables have identical relevant structures #

If every table (table1, table2, ..., table5) includes both racer_nr (the unique identifier for each racer) and total_time_spend (the time value you want to sum), the simplest way is to union all the tables first, then group and sum.

Example SQL Query #

SELECT 
    racer_nr,
    SUM(total_time_spend) AS total_time_across_all_tables
FROM (
    -- Union all rows from each table, keeping only the columns we need
    SELECT racer_nr, total_time_spend FROM table1
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table2
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table3
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table4
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table5
) AS combined_racer_data
GROUP BY racer_nr
-- Optional: Sort results by total time or racer number
ORDER BY total_time_across_all_tables DESC;

Key Notes for This Scenario #

• Use UNION ALL instead of UNION: UNION removes duplicate rows, which could accidentally drop valid time entries if a racer appears multiple times in the same table. UNION ALL preserves all rows, critical for accurate summing.
• Handle NULL values: If some rows have NULL for total_time_spend, use SUM(COALESCE(total_time_spend, 0)) to treat NULLs as 0 and avoid missing those entries.

Scenario 2: Tables have different structures (e.g., different column names) #

If your tables don’t share exact column names (e.g., one table uses race_id instead of racer_nr, or time_spent instead of total_time_spend), you’ll need to standardize the column names in each subquery before unioning.

Example SQL Query #

Suppose:

• table1, table2 use racer_nr and total_time_spend
• table3 uses race_id (matches racer_nr) and time_spent (matches total_time_spend)
• table4 has racer_id and session_time
• table5 has racer_number and total_session_time

The query would look like this:

SELECT 
    racer_nr,
    SUM(total_time_spend) AS total_time_across_all_tables
FROM (
    SELECT racer_nr, total_time_spend FROM table1
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table2
    UNION ALL
    SELECT race_id AS racer_nr, time_spent AS total_time_spend FROM table3
    UNION ALL
    SELECT racer_id AS racer_nr, session_time AS total_time_spend FROM table4
    UNION ALL
    SELECT racer_number AS racer_nr, total_session_time AS total_time_spend FROM table5
) AS combined_racer_data
GROUP BY racer_nr
ORDER BY racer_nr;

Bonus: Filtering before aggregating #

If you only want to include data from a specific date range or event, add WHERE clauses to each subquery:

SELECT 
    racer_nr,
    SUM(total_time_spend) AS total_time_across_all_tables
FROM (
    SELECT racer_nr, total_time_spend FROM table1 WHERE event_date >= '2024-01-01'
    UNION ALL
    SELECT racer_nr, total_time_spend FROM table2 WHERE event_date >= '2024-01-01'
    -- Repeat WHERE for other tables as needed
) AS combined_racer_data
GROUP BY racer_nr;

Performance Tips #

• Add indexes on racer_nr (or its equivalent) in each table: This speeds up the union and grouping operations, especially with large datasets.
• If working with extremely large tables, consider creating a temporary table to store combined data first, then query that for aggregation—but the above queries work fine for most use cases.

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