UNION 2表并删除基于某些列的重复项

Question

我正在用Python编写SQL查询，我正在尝试UNION 2个表并删除重复项(重复项被认为是字段"id"one_answers"product_id"中具有相同值的行(，我发现NOT EXISTS很有帮助，但我无法使其正常工作，我不知道问题出在哪里。这是我的SQL查询：

SELECT cc."id", cc."default_name", cc."slug", cpc."product_id", ce."value" 
FROM 
"catalog_category" AS cc, 
"catalog_product_categories" AS cpc, 
"catalog_categoryentitydetailvaluevarchar" AS ce
WHERE 
( 
cc."id" = cpc."category_id" 
AND cpc."product_id" IN {objects_id} 
AND cc."id" = ce."parent_object_id" 
AND cpc."category_id" = ce."parent_object_id" 
AND ce."shop" = 'shop_name' 
) 
UNION ALL 
SELECT cc2."id", cc2."default_name", cc2."slug", cpc2."product_id", ce2."value" 
FROM 
"catalog_category" AS cc2, 
"catalog_product_categories" AS cpc2, 
"catalog_categoryentitydetailvaluevarchar" AS ce2 
WHERE 
( 
cc2."id" = cpc2."category_id" 
AND cpc2."product_id" IN {objects_id} 
AND cc2."id" = ce2."parent_object_id" 
AND cpc2."category_id" = ce2."parent_object_id" 
AND ce2."shop" = 'default' 
AND NOT EXISTS(
SELECT 1 
FROM 
"catalog_category" AS cc, 
"catalog_product_categories" AS cpc, 
"catalog_categoryentitydetailvaluevarchar" AS ce 
WHERE 
cc2."id"=cc."id" 
AND cpc2."product_id"=cpc."product_id" 
)
) 

但结果是，使用NOT EXISTS语句，我只接收第一个SELECT中的值，而不使用NOT EXISTS，我接收两个表中所有重复的值。

Answer 1

您可以如下修改查询：

;With cte1 as (SELECT cc."id", cc."default_name", cc."slug", cpc."product_id", ce."value" 
FROM 
"catalog_category" AS cc, 
"catalog_product_categories" AS cpc, 
"catalog_categoryentitydetailvaluevarchar" AS ce
WHERE 
( 
cc."id" = cpc."category_id" 
AND cpc."product_id" IN {objects_id} 
AND cc."id" = ce."parent_object_id" 
AND cpc."category_id" = ce."parent_object_id" 
AND ce."shop" = 'shop_name' 
))
Select * from cte1 
UNION 
SELECT cc2."id", cc2."default_name", cc2."slug", cpc2."product_id", ce2."value" 
FROM 
"catalog_category" AS cc2, 
"catalog_product_categories" AS cpc2, 
"catalog_categoryentitydetailvaluevarchar" AS ce2 
WHERE 
( 
cc2."id" = cpc2."category_id" 
AND cpc2."product_id" IN {objects_id} 
AND cc2."id" = ce2."parent_object_id" 
AND cpc2."category_id" = ce2."parent_object_id" 
AND ce2."shop" = 'default' 
AND NOT EXISTS(
SELECT 1 
FROM 
Cte1                                WHERE 
cc2."id"=cte1."id" 
AND cpc2."product_id"=cte1."product_id" )