mysql - SQL如何获取不包含产品的类别？

Question

SELECT DISTINCT {c2.name} 
FROM 
    {BrandCategory AS c  
    JOIN CategoryProductRelation AS c2p  
    ON  {c.pk} = {c2p.source}  
    JOIN Product AS p  
    ON  {c2p.target} = {p.pk}  
    JOIN CategoryProductRelation AS c2p2  
    ON  {p.pk} = {c2p2.target}  
    JOIN TaxonomyCategory AS c2 ON {c2.pk} = {c2p2.source} 
    JOIN CatalogVersion AS cat 
    ON {c.catalogVersion} = {cat.PK}} 
WHERE 
    {c.code} = 'brand-MCH' 
    AND{cat.version} = 'Online' 
    AND {c2.code} NOT LIKE'%webFamily%' 
    AND {p.code} IN ('35365','34299')

以上查询提取包含产品“35365”和“34299”的分类类别

Result:
Passenger
Touring
All-Season
Truck

All Categories:
All-Season
Performance
Passenger
Winter
Touring
Summer
Truck
All-Terrain
Competition
Lawn

现在的要求是提取所有其他分类类别，即那些不包含产品“35365”和“34299”的类别

尝试 1:(失败)(不在)

SELECT  DISTINCT {c2.name}
    FROM  {BrandCategory AS c
    JOIN  CategoryProductRelation AS c2p  ON {c.pk} = {c2p.source}
    JOIN  Product AS p  ON {c2p.target} = {p.pk}
    JOIN  CategoryProductRelation AS c2p2  ON {p.pk} = {c2p2.target}
    JOIN  TaxonomyCategory AS c2  ON {c2.pk} = {c2p2.source}
    JOIN  CatalogVersion AS cat  ON {c.catalogVersion} = {cat.PK}}
    WHERE  {c.code} = 'brand-MCH' AND{cat.version} = 'Online'
      AND  {c2.code} NOT LIKE'%webFamily%'
      AND  {p.code} NOT IN ('35365','34299') 

尝试 2:(失败)(不存在)

SELECT  DISTINCT {c2.name}
    FROM  {BrandCategory AS c
    JOIN  CategoryProductRelation AS c2p  ON {c.pk} = {c2p.source}
    JOIN  Product AS p  ON {c2p.target} = {p.pk}
    JOIN  CategoryProductRelation AS c2p2  ON {p.pk} = {c2p2.target}
    JOIN  TaxonomyCategory AS c2  ON {c2.pk} = {c2p2.source}
    JOIN  CatalogVersion AS cat  ON {c.catalogVersion} = {cat.PK}}
    WHERE  {c.code} = 'brand-MCH' AND{cat.version} = 'Online'
      AND  {c2.code} NOT LIKE'%webFamily%'
      AND  {c2.code}
      AND  NOT EXISTS ({{
        SELECT  DISTINCT {c3.name}
            FROM  {BrandCategory AS c
            JOIN  CategoryProductRelation AS c2p  ON {c.pk} = {c2p.source}
            JOIN  Product AS p  ON {c2p.target} = {p.pk}
            JOIN  CategoryProductRelation AS c2p2  ON {p.pk} = {c2p2.target}
            JOIN  TaxonomyCategory AS c3  ON {c3.pk} = {c2p2.source}
            JOIN  CatalogVersion AS cat  ON {c.catalogVersion} = {cat.PK}}
            WHERE  {c3.name}={c2.name}
              AND  {c.code} = 'brand-MCH' AND{cat.version} = 'Online'
              AND  {c3.code} NOT LIKE'%webFamily%'
              AND  {p.code} IN ('35365','34299')}}
                      ) 

尝试 3:(失败)(不在子查询中)

SELECT  DISTINCT {c2.name}
    FROM  {BrandCategory AS c
    JOIN  CategoryProductRelation AS c2p  ON {c.pk} = {c2p.source}
    JOIN  Product AS p  ON {c2p.target} = {p.pk}
    JOIN  CategoryProductRelation AS c2p2  ON {p.pk} = {c2p2.target}
    JOIN  TaxonomyCategory AS c2  ON {c2.pk} = {c2p2.source}
    JOIN  CatalogVersion AS cat  ON {c.catalogVersion} = {cat.PK}}
    WHERE  {c.code} = 'brand-MCH' AND{cat.version} = 'Online'
      AND  {c2.code} NOT LIKE'%webFamily%'
      AND  {c2.name} NOT IN ({{
        SELECT  DISTINCT {c2.name}
            FROM  {BrandCategory AS c
            JOIN  CategoryProductRelation AS c2p  ON {c.pk} = {c2p.source}
            JOIN  Product AS p  ON {c2p.target} = {p.pk}
            JOIN  CategoryProductRelation AS c2p2  ON {p.pk} = {c2p2.target}
            JOIN  TaxonomyCategory AS c2  ON {c2.pk} = {c2p2.source}
            JOIN  CatalogVersion AS cat  ON {c.catalogVersion} = {cat.PK}}
            WHERE  {c.code} = 'brand-MCH' AND{cat.version} = 'Online'
              AND  {c2.code} NOT LIKE'%webFamily%'
              AND  {p.code} IN ('35365','34299')}}
                          ) 

...

Result:
All-Season
Performance
Passenger
Winter
Touring
Summer
Truck
All-Terrain
Competition
Lawn

Expected:
Performance
Winter
Summer
All-Terrain
Competition
Lawn

请帮助获取那些不包含的类别，并通过最佳查询保持性能控制。

此外，如果有一种方法可以在结果中获取带有某个标志的所有类别，以区分哪些产品包含真实或不真实的产品，那将是绝对的黄金，因为在这里我们会点击数据库两次来获取包含的类别，然后获取包含的类别不包含通过点播调用

注意:这些本质上是 SQL 查询，但只是用这些大括号进行了稍微修改，以支持 Hybris 框架中的灵活搜索查询格式

Answer 1

查询-
1. 使用 NOT IN-
SELECT {c.code}
从
{产品为p
在 {c2p.target} = {p.pk} 上将 CategoryProductRelation 作为 c2p 加入
将类别加入为 {c2p.source} = {c.pk}} 上的 c
在哪里
{p.code} NOT IN ('35365','34299')

使用 NOT EXISTS-
SELECT {c.code}
从
{CategoryProductRelation 作为 c2p
将类别加入为 {c2p.source} = {c.pk}} 上的 c
在哪里
不存在 ({{SELECT {p.code} FROM {Product as p} WHERE {p.code} IN ('35365','34299') and {c2p.target}={p.pk}}})

使用子查询-
SELECT {c.code}
从
{产品为p
在 {c2p.target} = {p.pk} 上将 CategoryProductRelation 作为 c2p 加入
将类别加入为 {c2p.source} = {c.pk}} 上的 c
在哪里
{p.pk} IN ({{SELECT {pk} FROM {Product} WHERE {code} IN ('35365','34299')}})