SQL如何将同表同一列按不同Field ID视为不同列并求和校验？

Got it, let's break this down clearly. First, I notice your table stores per-field dollar values, but the ID column looks like just a row identifier—we’ll need a way to pair the right field values together (like matching Field ID 123 and 543 for the same user). I’ll assume your table has a user_id column (this is standard for this kind of per-user field data; if that’s not the case, let me know and we can adjust!). Let’s name the table user_field_amounts for clarity.

Method 1: Conditional Aggregation (Most Flexible) #

This approach works even if some users don’t have values for one of the fields (it treats missing values as 0) and is easy to extend if you need to add more fields later.

SELECT
  user_id,
  -- Calculate the sum of Field ID 123 and 543 values
  SUM(CASE WHEN field_id = 123 THEN dollar_value ELSE 0 END) +
  SUM(CASE WHEN field_id = 543 THEN dollar_value ELSE 0 END) AS combined_total,
  -- Validate against your specified value (e.g., 45.00 here)
  CASE
    WHEN (SUM(CASE WHEN field_id = 123 THEN dollar_value ELSE 0 END) +
          SUM(CASE WHEN field_id = 543 THEN dollar_value ELSE 0 END)) = 45.00
    THEN '✅ Match'
    ELSE '❌ No Match'
  END AS validation_status
FROM user_field_amounts
WHERE field_id IN (123, 543) -- Filter to only the fields we care about
GROUP BY user_id;

How it works: #

• SUM(CASE ...) isolates the total value for each field per user
• We add those two totals together to get the combined amount
• The CASE statement compares this sum to your target value and returns a clear validation result

Method 2: Self-Join (For Exact Pairs) #

If you only want to return users who have values for both fields (no missing entries), a self-join is a clean option:

SELECT
  u1.user_id,
  u1.dollar_value + u2.dollar_value AS combined_total,
  CASE
    WHEN u1.dollar_value + u2.dollar_value = 45.00 THEN '✅ Match'
    ELSE '❌ No Match'
  END AS validation_status
FROM user_field_amounts u1
INNER JOIN user_field_amounts u2
  ON u1.user_id = u2.user_id
  AND u1.field_id = 123
  AND u2.field_id = 543;

How it works: #

• We join the table to itself, matching rows where the user is the same, and one row is Field ID 123 while the other is 543
• This only returns users who have entries for both fields (since it’s an INNER JOIN)

If You Need to Target Specific Rows (Hardcoded Example) #

If you just want to test a specific pair of rows (like ID 1 and ID 3 from your sample data), you can use subqueries to pull those values directly:

SELECT
  (SELECT dollar_value FROM user_field_amounts WHERE id = 1) +
  (SELECT dollar_value FROM user_field_amounts WHERE id = 3) AS combined_total,
  CASE
    WHEN (SELECT dollar_value FROM user_field_amounts WHERE id = 1) +
         (SELECT dollar_value FROM user_field_amounts WHERE id = 3) = 45.00
    THEN '✅ Match'
    ELSE '❌ No Match'
  END AS validation_status;

Note: This is only useful for one-off checks—for dynamic, per-user validation, stick to the first two methods.

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