跨表查询并执行行相乘：Table A与Table B的数值计算需求

Got it, let's walk through how to solve this problem—whether you're working with database tables (using SQL) or spreadsheets (like Excel). I'll use concrete examples to make it easy to follow.

SQL (Database Tables) #

First, let's define sample tables to clarify the structure:

Sample Table A #

Sample Table B #

The goal is to multiply each row's Total in Table A with the matching STD_Time from Table B for the same activity.

Query Solution #

Use a JOIN to link the two tables on the Activity column, then compute the product:

SELECT 
    a.Activity,
    a.Total,
    b.STD_Time,
    a.Total * b.STD_Time AS Calculated_Result
FROM 
    Table_A a
INNER JOIN 
    Table_B b ON a.Activity = b.Activity
WHERE 
    b.Activity IN ('Activity 1', 'Activity 2');

Breakdown: #

• INNER JOIN Table_B b ON a.Activity = b.Activity: Connects each row in Table A to the corresponding activity row in Table B.
• a.Total * b.STD_Time AS Calculated_Result: Calculates the product and assigns a clear name to the result column.
• The WHERE clause filters to only the activities we need (optional if your tables don't have extra activities).

If Table A doesn't have an Activity column:

If Table A just has two rows (one for each activity) without an explicit Activity label, you can map manually using row numbers:

SELECT 
    CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN 'Activity 1'
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 2 THEN 'Activity 2'
    END AS Activity,
    Total,
    CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN (SELECT STD_Time FROM Table_B WHERE Activity = 'Activity 1')
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 2 THEN (SELECT STD_Time FROM Table_B WHERE Activity = 'Activity 2')
    END AS STD_Time,
    Total * CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN (SELECT STD_Time FROM Table_B WHERE Activity = 'Activity 1')
        WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 2 THEN (SELECT STD_Time FROM Table_B WHERE Activity = 'Activity 2')
    END AS Calculated_Result
FROM Table_A;

Note: The ORDER BY (SELECT NULL) is a placeholder—adjust the ordering if your Table A rows have a specific sequence you need to respect.

Excel (Spreadsheets) #

If you're working with Excel or Google Sheets, here's how to do it:

Assume Spreadsheet Layout: #

• Table A: Headers in A1 (Activity) and B1 (Total); rows 2 and 3 have "Activity 1" / 100 and "Activity 2" / 150.
• Table B: Headers in D1 (Activity) and E1 (STD Time); rows 2 and 3 have "Activity 1" / 2.5 and "Activity 2" / 3.0.

Formula Solution #

In cell C2 (next to Table A's Total for Activity 1), enter this formula:

=B2 * VLOOKUP(A2, $D$2:$E$3, 2, FALSE)

Then drag the fill handle down to cell C3 to apply it to Activity 2.

Breakdown: #

• VLOOKUP(A2, $D$2:$E$3, 2, FALSE): Looks up the Activity in A2 in Table B, returns the corresponding STD Time from column 2.
• Multiplying that by B2 (the Total) gives your calculated result.

If Table A doesn't have an Activity column:

If you just have two rows of Total values without labels, you can directly reference the STD Time cells:

• For Activity 1 row: =B2 * E2
• For Activity 2 row: =B3 * E3

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