通过JOIN查询识别PRODUCT_CONVERSION表中的缺失数据

需要关联PRODUCT表与ORDER表，找出PRODUCT_CONVERSION表中的缺失数据。规则要求每个产品在PRODUCT_CONVERSION表中必须存在两条记录：

• 第一条：以Product.BaseUoM为FROM_UOM，固定值"ST"为TO_UOM
• 第二条：以Product.BaseUoM为FROM_UOM，Order.OrderUoM为TO_UOM

当前使用以下查询未得到预期结果：

select distinct P.PRODUCT,BASE_UOM,TXN_UOM,BASE_UOM_CODE,ALTERNATE_UOM_CODE,BASE_QUANTITY,ALTERNATE_QUANTITY
from ORDER T 
join PRODUCT P on P.PRODUCT=T.PRODUCT 
full outer join PRODUCT_CONVERSION PC on PC.PRODUCT_ID=T.PRODUCT
where 
PC.PRODUCT_ID is null
AND
(BASE_UOM<>TXN_UOM or BASE_UOM<>'ST') 
order by P.PRODUCT

示例数据 #

Product表 #

Order表 #

Product Conversion表（缺失一条记录） #

预期存在的Product 1 | EA | ME1 | 1 | 0.8记录缺失

预期查询结果 #

需显示上述缺失的记录

原查询的问题 #

1. 关联逻辑不精准：全外关联仅匹配产品ID，未关联FROM_UOM和TO_UOM，无法定位具体缺失的转换记录
2. 过滤条件错误：PC.PRODUCT_ID is null仅能筛选完全没有转换记录的产品，无法识别已存在部分转换但缺失指定条目的情况
3. 条件逻辑混乱：(BASE_UOM<>TXN_UOM or BASE_UOM<>'ST')的判断不符合规则要求，无法区分需要检查的两类转换记录

修正后的查询语句 #

WITH required_conversions AS (
    -- 生成每个产品必须的两条转换记录模板
    SELECT
        P.PRODUCT,
        P.BaseUom AS FROM_UOM,
        'ST' AS TO_UOM,
        O.OrderUOM AS TXN_UOM
    FROM PRODUCT P
    JOIN ORDER O ON P.PRODUCT = O.PRODUCT
    UNION ALL
    SELECT
        P.PRODUCT,
        P.BaseUom AS FROM_UOM,
        O.OrderUOM AS TO_UOM,
        O.OrderUOM AS TXN_UOM
    FROM PRODUCT P
    JOIN ORDER O ON P.PRODUCT = O.PRODUCT
)
SELECT
    rc.PRODUCT,
    rc.FROM_UOM,
    rc.TO_UOM,
    rc.TXN_UOM,
    PC.BASE_QUANTITY,
    PC.ALTERNATE_QUANTITY
FROM required_conversions rc
LEFT JOIN PRODUCT_CONVERSION PC 
    ON rc.PRODUCT = PC.PRODUCT 
    AND rc.FROM_UOM = PC."FROM UOM" 
    AND rc.TO_UOM = PC."TO UOM"
ORDER BY rc.PRODUCT, rc.TO_UOM;

逻辑说明 #

1. 生成必填转换模板：通过UNION ALL生成每个产品必须存在的两条转换记录（对应ST和OrderUOM的条目）
2. 左关联匹配现有数据：将模板表与PRODUCT_CONVERSION按产品、FROM_UOM、TO_UOM三个维度左关联
3. 展示全部必填条目：无论现有转换记录是否存在，都会显示模板中的条目，缺失的记录会以NULL填充数量字段，完全符合预期结果要求

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