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"one_answers" 34299"'
的分类类别类别Result:
Passenger
Touring
All-Season
Truck
All Categories:
All-Season
Performance
Passenger
Winter
Touring
Summer
Truck
All-Terrain
Competition
Lawn
现在的要求是删除所有其他分类类别,这意味着那些不包含产品" 35365"one_answers" 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
请帮助获得那些不包含最佳查询的类别。
另外,如果有一种方法可以获取所有类别具有某些标志的结果,以区分哪些产品包含哪些产品,那将是绝对的黄金,因为我们在这里击中DB两次以获取包含的类别,然后类别不要通过按需呼叫
包含注意:这些本质上是SQL查询,但只是用这些括号稍作修改,以支持Hybris Framework中的灵活搜索查询格式
Queries
-
1.不使用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')
-
使用不存在 -
SELECT {c.code} FROM {CategoryProductRelation as c2p join Category as c on {c2p.source} = {c.pk}} WHERE NOT 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 p join 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')}})
首先查看NOT EXISTS
和NOT IN
是否给出正确的值。
对于EXISTS
,不要打扰DISTINCT
,也不要指定列。这足够:
NOT EXISTS ( SELECT 1 FROM ... )
删除与问题无关的所有JOIN
。这将使您更容易看到错误。
什么是牙套?也许您应该删除[mysql]
标签?
我认为这太容易了:
使用Northwnd
- 首先:选择类别的查询没有任何产品
选择类别。CategoryId,categoryName,productid,productName从产品正确地加入类别上的类别。其中(Productid为null)
- 第二:选择产品的查询不遵循任何类别
选择类别。CategoryId,categoryName,productid,productname从左JON类别上的产品中。其中(category.CategoryId为null)