多表同名列按相同ID求和的SQL实现请求

Hey there! I totally get how urgent this is—let's break down exactly how to sum columns with matching IDs and names across your three tables, using the most common tools you might be working with.

If your tables are stored in a relational database, the simplest approach is to combine all three tables first, then group by the id and name columns to calculate the sum.

Assuming your tables are named table1, table2, table3, each with columns id, name, and the numeric column(s) you want to sum (e.g., value), here's the query:

SELECT
    id,
    name,
    SUM(value) AS total_value
FROM (
    -- Combine all three tables (keeps all rows, including duplicates)
    SELECT id, name, value FROM table1
    UNION ALL
    SELECT id, name, value FROM table2
    UNION ALL
    SELECT id, name, value FROM table3
) AS combined_tables
-- Group by the columns you want to match
GROUP BY id, name
-- Optional: Order results for readability
ORDER BY id, name;

If you have multiple columns to sum (like value1 and value2), just add additional SUM() clauses:

SELECT
    id,
    name,
    SUM(value1) AS total_value1,
    SUM(value2) AS total_value2
FROM (
    SELECT id, name, value1, value2 FROM table1
    UNION ALL
    SELECT id, name, value1, value2 FROM table2
    UNION ALL
    SELECT id, name, value1, value2 FROM table3
) AS combined_tables
GROUP BY id, name
ORDER BY id, name;

If you're working with data frames in Python, concatenate the three tables first, then group and sum.

First, load your data (replace the read method with your actual data source, like CSV, Excel, or a database connection):

import pandas as pd

# Load your three data frames
df1 = pd.read_csv("table1.csv")
df2 = pd.read_csv("table2.csv")
df3 = pd.read_csv("table3.csv")

# Combine all three into a single data frame
combined_df = pd.concat([df1, df2, df3], ignore_index=True)

# Group by 'id' and 'name', then sum all numeric columns
result_df = combined_df.groupby(['id', 'name']).sum().reset_index()

# Export or view the result
print(result_df)
result_df.to_csv("summed_result.csv", index=False)

If you only want to sum specific columns (not all numeric ones), specify them explicitly:

# Sum only 'value1' and 'value2'
result_df = combined_df.groupby(['id', 'name'])[['value1', 'value2']].sum().reset_index()

If you prefer a no-code approach, Excel has two straightforward methods:

Option A: Pivot Table (Quickest & Most Intuitive) #

1. Combine your tables: Copy all rows from Table2 and Table3, then paste them directly below the rows of Table1 (ensure column headers match exactly across all tables).
2. Insert a Pivot Table:
   • Select the entire combined dataset (including headers).
   • Go to the Insert tab > Click PivotTable.
   • In the PivotTable Fields pane:
     • Drag id and name to the Rows area.
     • Drag the column you want to sum to the Values area (Excel will default to Sum for numeric columns; if not, right-click the value field > Value Field Settings > Choose Sum).
   • Your pivot table will instantly show the total sum for each unique id + name pair.

Option B: SUMIFS Function #

1. Create a list of unique pairs: Copy the combined id and name columns to a new sheet, then use Data > Remove Duplicates to get unique id + name pairs.
2. Use SUMIFS to calculate sums:
   • In the new sheet, next to the first unique pair, enter this formula (adjust ranges to match your data):

     =SUMIFS(Combined!$C:$C, Combined!$A:$A, A2, Combined!$B:$B, B2)
     

     • Combined!$C:$C = the column you want to sum
     • Combined!$A:$A = the id column in your combined table
     • A2 = the unique id in your new sheet
     • Combined!$B:$B = the name column in your combined table
     • B2 = the unique name in your new sheet
   • Drag the formula down to apply it to all unique pairs.

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