SQL Server关联表查询需求：计算指定条件下匹配值总和

Got it, let's break down these two SQL Server scenarios step by step:

1. Sum Col1 in Table1 based on Col2 conditions in Table2 #

First, we need to link Table1 and Table2 (I'll assume they share a common identifier like Col1—swap this out if your actual join column is different). Here are two straightforward approaches:

Option 1: Use INNER JOIN #

This is great if you only want to sum rows in Table1 that have a matching row in Table2 that meets your condition:

SELECT SUM(t1.Col1) AS TotalCol1Value
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t2.Col2 = 'YourConditionValue'; -- Replace with your actual condition (e.g., > 100, LIKE '%Active%')

Option 2: Use a subquery #

If you prefer to explicitly filter Table2 first before joining, this works too:

SELECT SUM(Col1) AS TotalCol1Value
FROM Table1
WHERE Col1 IN (
    SELECT Col1
    FROM Table2
    WHERE Col2 = 'YourConditionValue' -- Same condition as above
);

2. Sum values when matching strings (with Col1 as shared ID) #

From your description, I'm assuming you want to sum values in one table when the linked row in the other table matches a specific string pattern. Let's say we want to sum Table1.Col1 where Table2.Col3 matches our target string—here's how to do it:

SELECT SUM(t1.Col1) AS MatchedStringTotal
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
-- Use LIKE for fuzzy matches, = for exact matches
WHERE t2.Col3 LIKE '%TargetString%'; -- Replace with your actual string condition

If you meant summing the length of matching strings instead, just adjust the SELECT clause:

-- Example: Sum the length of Table1.Col2 where Table2.Col3 matches a string
SELECT SUM(LEN(t1.Col2)) AS TotalMatchedStringLength
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t2.Col3 = 'ExactMatchString';

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