SQL中双共同列关联表及合并去重方案咨询

Great question—you’re already thinking in the right direction with using LedgerID + Year as a composite key. Let’s break this into two parts: first the general approach to joining tables with shared columns, then the specific fix for avoiding duplicate Balance values in your Table C output.

1. 通用：连接两表并合并其余数据的核心方法 #

When you need to combine tables that share common columns (like your LedgerID and Year) and merge their remaining data, the key is choosing the right join type and using functions to handle missing values:

• INNER JOIN: Use this if you only want rows where the composite key exists in both tables (the intersection of the two datasets).
• FULL OUTER JOIN: Use this if you need to retain all rows from either table (even if the key only exists in one table)—this is probably what you need since you mentioned merging data present in left or right tables.
• COALESCE(): This function lets you pull the first non-null value from corresponding columns, which is critical for merging columns that might be missing in one table (e.g., if a row exists only in Table B, COALESCE(a.Balance, 0) could set a default Balance instead of showing NULL).

2. 具体：合并Table A/B得到Table C并避免Balance重复 #

Your concern about duplicate Balance = 50.00 values across Tires/Windshield rows makes sense—this happens because a single Balance value in Table A gets repeated for every matching ExpenseType row in Table B. Here’s the best SQL approach to fix this, depending on whether you want to adjust display or data storage:

方案1：调整查询显示（避免重复显示Balance） #

If you just don’t want the same Balance value showing up in every row for the same LedgerID + Year group, use a window function like ROW_NUMBER() to only display the Balance once per group:

WITH combined_data AS (
    -- First, join the tables with your composite key
    SELECT
        COALESCE(a.LedgerID, b.LedgerID) AS LedgerID,
        COALESCE(a.Year, b.Year) AS Year,
        a.Balance,
        b.ExpenseType,
        -- Assign a row number to each row in the same LedgerID+Year group
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(a.LedgerID, b.LedgerID), COALESCE(a.Year, b.Year)
            ORDER BY b.ExpenseType -- Order by ExpenseType to pick a consistent first row
        ) AS row_num
    FROM TableA a
    FULL OUTER JOIN TableB b
        ON a.LedgerID = b.LedgerID
        AND a.Year = b.Year
)
-- Now, only show Balance for the first row in each group
SELECT
    LedgerID,
    Year,
    CASE WHEN row_num = 1 THEN Balance ELSE NULL END AS Balance,
    ExpenseType
FROM combined_data
ORDER BY LedgerID, Year, ExpenseType;

方案2：优化数据建模（避免重复存储Balance） #

If you’re worried about redundant data in your database (not just display), restructure your tables to follow normalization rules:

• Keep LedgerID, Year, and Balance in a single table (like your existing Table A).
• Keep LedgerID, Year, and ExpenseType in Table B.
• When querying, join them on the composite key—this way, Balance is only stored once per LedgerID + Year, and you can still retrieve all associated ExpenseTypes without repeating the value.

为什么这比单纯的内连接+合并更好？ #

Your initial thought of using an inner join plus merging left/right data is essentially what a FULL OUTER JOIN does natively. The window function adds the critical logic to suppress duplicate Balance displays, which isn’t handled by basic joins alone.

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