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 Framework中的灵活搜索查询格式

最佳答案

查询-
1.使用NOT IN- SELECT {c.code}FROM{Product as p join CategoryProductRelation as c2p on {c2p.target} = {p.pk} join Category as c on {c2p.source} = {c.pk}}WHERE{p.code} NOT IN ('35365','34299')


使用NOT EXISTS- SELECT {c.code}FROM{CategoryProductRelation as c2pjoin Category as c on {c2p.source} = {c.pk}}WHERENOT EXISTS ({{SELECT {p.code} FROM {Product as p} WHERE {p.code} IN ('35365','34299') and {c2p.target}={p.pk}}})
使用子查询-SELECT {c.code}FROM{Product as pjoin CategoryProductRelation as c2p on {c2p.target} = {p.pk}join Category as c on {c2p.source} = {c.pk}}WHERE{p.pk} IN ({{SELECT {pk} FROM {Product} WHERE {code} IN ('35365','34299')}})

关于mysql - SQL如何获取不包含产品的类别?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54966777/

10-11 02:47