如何使用EXCEPT查询比较同结构表数据并判断是否匹配？

Hey there! Let's tackle this problem step by step. First off, I notice a critical issue in your original query: the column order in your two SELECT statements doesn't match, which is mandatory for EXCEPT to work correctly (it compares columns positionally, not by name). That's probably why you're running into issues getting meaningful results.

Step 1: Fix Column Alignment for EXCEPT #

First, let's reorder the columns in your second query to exactly match the first. This ensures EXCEPT compares the right values against each other:

-- Corrected base EXCEPT query to compare matching columns
SELECT 
  CATEGORY_ID,
  PERIOD_CODE,
  RETAIL_ID,
  Transhipment_Ind,
  EQ_VOLUME,
  CONSUMER_UNITS
FROM vwAGG_CAT_STORE
EXCEPT
SELECT 
  P.CATEGORY_ID,
  PERIOD_CODE,
  RETAIL_ID,
  TRANSSHIPMENT_IND,
  SUM(EQ_VOLUME) AS EQ_VOLUME, -- Match column name from first query
  SUM(CONSUMER_UNITS) AS CONSUMER_UNITS
FROM vwFCT_DISTSTORE F
INNER JOIN vwDIM_PROD P ON P.PRODUCT_ID = F.PRODUCT_ID
GROUP BY P.CATEGORY_ID, PERIOD_CODE, RETAIL_ID, TRANSSHIPMENT_IND;

Step 2: Add Match Status判定 #

Now we can build on this to get a clear "match/no match" result, or detailed differences if you need them.

Option 1: Simple Match/No Match Status

If you just want a high-level status on whether the datasets are identical, wrap the EXCEPT query in a count check:

SELECT 
  CASE 
    WHEN COUNT(*) = 0 THEN '✅ Datasets Match'
    ELSE '❌ Datasets Do NOT Match' 
  END AS Match_Status
FROM (
  -- Reuse the corrected EXCEPT query above
  SELECT 
    CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    Transhipment_Ind,
    EQ_VOLUME,
    CONSUMER_UNITS
  FROM vwAGG_CAT_STORE
  EXCEPT
  SELECT 
    P.CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    TRANSSHIPMENT_IND,
    SUM(EQ_VOLUME) AS EQ_VOLUME,
    SUM(CONSUMER_UNITS) AS CONSUMER_UNITS
  FROM vwFCT_DISTSTORE F
  INNER JOIN vwDIM_PROD P ON P.PRODUCT_ID = F.PRODUCT_ID
  GROUP BY P.CATEGORY_ID, PERIOD_CODE, RETAIL_ID, TRANSSHIPMENT_IND
) AS Differences;

Option 2: Detailed Differences + Status

Note that EXCEPT only returns rows from the first query missing in the second. To get all differences (including rows in the aggregated dataset missing from the first view), use EXCEPT ALL (preserves duplicates) combined with UNION ALL, plus a flag to show where the discrepancy is:

-- Rows present in vwAGG_CAT_STORE but missing from aggregated vwFCT_DISTSTORE
SELECT 
  *,
  'Missing from Aggregated vwFCT_DISTSTORE' AS Difference_Type
FROM (
  SELECT 
    CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    Transhipment_Ind,
    EQ_VOLUME,
    CONSUMER_UNITS
  FROM vwAGG_CAT_STORE
  EXCEPT ALL
  SELECT 
    P.CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    TRANSSHIPMENT_IND,
    SUM(EQ_VOLUME) AS EQ_VOLUME,
    SUM(CONSUMER_UNITS) AS CONSUMER_UNITS
  FROM vwFCT_DISTSTORE F
  INNER JOIN vwDIM_PROD P ON P.PRODUCT_ID = F.PRODUCT_ID
  GROUP BY P.CATEGORY_ID, PERIOD_CODE, RETAIL_ID, TRANSSHIPMENT_IND
) AS LeftDifferences

UNION ALL

-- Rows present in aggregated vwFCT_DISTSTORE but missing from vwAGG_CAT_STORE
SELECT 
  *,
  'Missing from vwAGG_CAT_STORE' AS Difference_Type
FROM (
  SELECT 
    P.CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    TRANSSHIPMENT_IND,
    SUM(EQ_VOLUME) AS EQ_VOLUME,
    SUM(CONSUMER_UNITS) AS CONSUMER_UNITS
  FROM vwFCT_DISTSTORE F
  INNER JOIN vwDIM_PROD P ON P.PRODUCT_ID = F.PRODUCT_ID
  GROUP BY P.CATEGORY_ID, PERIOD_CODE, RETAIL_ID, TRANSSHIPMENT_IND
  EXCEPT ALL
  SELECT 
    CATEGORY_ID,
    PERIOD_CODE,
    RETAIL_ID,
    Transhipment_Ind,
    EQ_VOLUME,
    CONSUMER_UNITS
  FROM vwAGG_CAT_STORE
) AS RightDifferences;

Quick Tips #

• Use EXCEPT ALL instead of EXCEPT if your data has duplicate rows (regular EXCEPT removes duplicates, which can hide real count discrepancies).
• Double-check that corresponding columns have identical data types (e.g., Transhipment_Ind and TRANSSHIPMENT_IND should match in type—case differences in names usually don't matter, but types do).

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