如何在开源SQL中直接提取数据计算并关联保存至新表？

Absolutely! SQL is made for exactly this kind of in-database data transformation—you don’t need to export your data to Excel, Python, or any other tool and then re-import it. Given your Excel/VBA/Python background, you’ll recognize a lot of the logic here, just adapted to database-native operations.

Here’s a step-by-step breakdown using open-source SQL databases (like MySQL, PostgreSQL, or SQLite—all free and perfect for your use case):

1. Set Up Your Target Table (With Foreign Key) #

First, you’ll need a table to store your calculated results, linked to the original records via a foreign key. This is similar to setting up a new sheet in Excel with a column that references IDs from your source sheet.

Example SQL (adjust syntax slightly for your specific database):

-- For MySQL/MariaDB
CREATE TABLE calculated_results (
    result_id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-generates unique IDs (like Excel's fill handle)
    record_id INT NOT NULL,
    total_value DECIMAL(12,2), -- Stores your arithmetic result
    average_value DECIMAL(12,2),
    FOREIGN KEY (record_id) REFERENCES original_records(record_id) -- Links back to source table
);

-- For PostgreSQL, replace AUTO_INCREMENT with SERIAL or GENERATED AS IDENTITY
-- CREATE TABLE calculated_results (
--     result_id SERIAL PRIMARY KEY,
--     record_id INT NOT NULL REFERENCES original_records(record_id),
--     total_value NUMERIC(12,2),
--     average_value NUMERIC(12,2)
-- );

2. Insert Calculated Results Directly #

The magic happens with the INSERT ... SELECT statement. This lets you query your source records, run arithmetic calculations on the fly, and insert everything straight into your target table—all in one database operation.

This is equivalent to writing an Excel formula like =A2+B2 and dragging it down, then copying the results to a new sheet—except it’s automated and runs directly on the database (way faster for large datasets).

Example:

INSERT INTO calculated_results (record_id, total_value, average_value)
SELECT
    record_id,
    value1 + value2 + value3 AS total_value, -- Basic addition
    (value1 + value2 + value3)/3 AS average_value -- Average calculation
FROM original_records
WHERE record_id BETWEEN 101 AND 200; -- Filter to select your target group of records

3. Update Results (If Source Data Changes) #

If your original numeric records get updated and you need to refresh the calculated values, use an UPDATE with a JOIN to link the source and target tables. This is like refreshing a pivot table or re-running a Python script to recalculate values.

Example:

UPDATE calculated_results cr
JOIN original_records nr ON cr.record_id = nr.record_id
SET
    cr.total_value = nr.value1 + nr.value2 + nr.value3,
    cr.average_value = (nr.value1 + nr.value2 + nr.value3)/3
WHERE nr.record_id BETWEEN 101 AND 200; -- Update only the relevant records

• Arithmetic operations: SQL supports all the basics (+, -, *, /) plus functions like SUM(), AVG(), ROUND()—think of these as Excel’s built-in functions, but optimized for databases.
• Conditional logic: If you need something like Excel’s IF() function, use SQL’s CASE statement to add conditional calculations.
• Custom functions: For more complex logic (like a Python function), most open-source databases let you create User-Defined Functions (UDFs) in languages like Python (PostgreSQL) or JavaScript (MySQL).

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