如何统计两张同表不同日期的差异数量？求SQL代码修复

Let's break down what's going wrong with your current query and fix it to get the data change stats you need.

First, the Issues in Your Original Code #

• Typos: You wrote a.num = b.bum — I'm guessing that's a typo for b.num (since both tables are versions of the same source, the join key should match). This alone would cause a "column not found" error.
• Misuse of count(): When you use count(a.color) inside a CASE statement, it counts all non-null values of a.color in the group, not just the rows where a.color != b.color. That's why your results won't reflect actual changes.
• Limited Join Scope: A RIGHT JOIN only keeps records from the 100720 (B) table. If there are new of_key values in 130720 (A) that don't exist in B, they'll be excluded. For full change tracking, you probably want a FULL OUTER JOIN.
• Grouping on Nulls: With a RIGHT JOIN, a.of_key can be NULL (when B has a key A doesn't). Grouping on a.of_key will lump all these nulls into a single group, which isn't useful.

Corrected Queries #

Choose the one that matches your exact needs:

1. Count Number of Rows with Changes per of_key

This counts how many rows have a differing color or side for each of_key:

SELECT 
    COALESCE(a.of_key, b.of_key) AS of_key,
    SUM(CASE 
        WHEN a.color != b.color THEN 1 
        ELSE 0 
    END) AS color_change_count,
    SUM(CASE 
        WHEN a.side != b.side THEN 1 
        ELSE 0 
    END) AS side_change_count
FROM 130720 A
FULL OUTER JOIN 100720 B 
    ON a.of_key = b.of_key 
    AND a.num = b.num  -- Fixed the typo here
GROUP BY COALESCE(a.of_key, b.of_key)

2. Flag Whether Any Change Occurred per of_key

If you just want to know if any row for an of_key has a change (instead of counting how many), use MAX() instead:

SELECT 
    COALESCE(a.of_key, b.of_key) AS of_key,
    MAX(CASE 
        WHEN a.color != b.color THEN 1 
        ELSE 0 
    END) AS has_color_change,
    MAX(CASE 
        WHEN a.side != b.side THEN 1 
        ELSE 0 
    END) AS has_side_change
FROM 130720 A
FULL OUTER JOIN 100720 B 
    ON a.of_key = b.of_key 
    AND a.num = b.num
GROUP BY COALESCE(a.of_key, b.of_key)

Key Notes #

• COALESCE(a.of_key, b.of_key) ensures we use the valid of_key from either table, even if one side is null.
• FULL OUTER JOIN includes all of_key values from both tables, so you'll catch new records in A and deleted records in B (you can add additional CASE statements to flag those if needed).
• Using SUM(1) instead of count() correctly tallies only the rows where the field differs.

内容的提问来源于stack exchange，提问作者M.R