Выявление отсутствующих данных в таблицах SQL с помощью соединений
Я пытаюсь определить данные, отсутствующие в таблице PRODUCT_CONVERSION, в сочетании с таблицами PRODUCT и ORDER.
Как правило, для каждого продукта в таблице 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
Товар
База продуктовUom
Продукт 1 EA
Заказать
ЗАКАЗ ПРОДУКТАUOM
Продукт 1 ME1
Конверсия продукта
ПРОДУКТ ОТ ЕДИНИЦЫ ДО ЕДИНИЦЫ БАЗОВОЕ КОЛ-ВО АЛЬТЕРНАТИВНОЕ КОЛ-ВО
Товар 1 EA ST 1 0.5
Продукт 1 EA ME1 1 0.8 Эта строка ожидается, но допустим, что она отсутствует
Ожидаемый результат от объединения всех столов:
ПРОДУКТ ОТ ЕДИНИЦЫ ДО ЕДИНИЦЫ TXN ЕДИНИЦА БАЗОВОЕ КОЛ-ВО АЛЬТЕРНАТИВНОЕ КОЛ-ВО
Продукт 1 EA ST ME1 1 0,5
Продукт 1 EA ME1 ME1 NULL NULL Поэтому я ожидаю, что в моем результате эта строка, показывающая, что это отсутствует
@jarlh спасибо за выделение, я изменил это раньше, чтобы что-то проверить, теперь исправил это.
Отсутствие классификации столбцов и образцов данных (изображения не учитываются) или ожидаемых результатов делают это очень трудным для нас, чтобы мы могли помочь вам здесь.
Ответы 2
Я обычно предпочитаю использовать коррелированные подзапросы для решения такого рода проблем.
Преимущество для меня заключается в том, что я могу легко разделить различные «условия» для проверки и обрабатывать логику отдельно, фактически не беспокоясь о соединениях и последствиях расширения строк и т. д.
SELECT P.PRODUCT_ID
FROM PRODUCT P
WHERE NOT EXISTS (SELECT 1
FROM PRODUCT_CONVERSION PC
WHERE PC.TO_UOM = 'ST'
AND P.BASEUOM = PC.FROM_UOM)
Теперь мы можем добавить и второе условие,
SELECT P.PRODUCT_ID
FROM PRODUCT P
WHERE NOT EXISTS (SELECT 1
FROM PRODUCT_CONVERSION PC
WHERE PC.TO_UOM = 'ST'
AND P.BASEUOM = PC.FROM_UOM)
OR NOT EXISTS (SELECT 1
ORDERS O ON P.PRODUCT_ID = O.PRODUCT_ID
INNER JOIN PRODUCT_CONVERSION PC
ON PC.TO_UOM = 'ST'
AND P.BASEUOM = PC.FROM_UOM)
)
Используйте левое соединение, чтобы найти недостающую запись.
SELECT a.MATERIAL, a.B_UOM, a.A_UOM, a.B_QTY, a.A_QTY
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON a.MATERIAL = b.MATERIAL
AND a.B_UOM = b.B_UOM
AND a.A_UOM = b.A_UOM
WHERE b.MATERIAL IS NULL
используйте запрос на слияние, чтобы вставить отсутствующие записи.
MERGE TABLE3 AS TARGET
USING
(SELECT a.MATERIAL, a.B_UOM, a.A_UOM, a.B_QTY, a.A_QTY
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON a.MATERIAL = b.MATERIAL
AND a.B_UOM = b.B_UOM
AND a.A_UOM = b.A_UOM
WHERE b.MATERIAL IS NULL
) AS SOURCE
ON SOURCE.MATERIAL = TARGET.PRODUCT
AND SOURCE.B_UOM = TARGET.FROM_UOM
AND SOURCE.A_UOM = TARGET.TO_UOM
WHEN NOT MATCHED
THEN
INSERT (PRODUCT, FROM_UOM, TO_UOM,FROM_QUANTITY,TO_QUANTITY)
VALUES (MATERIAL,B_UOM,A_UOM,B_QTY,A_QTY);
full outer joinв сочетании сwhere PC.PRODUCT_ID is not nullявляется противоречием.