Hybris灵活的搜索联合查询以获取产品

Question

code    Attribute1(String)
A         C
B         D
C         Empty
D         Empty 

如何获得所有A、B、C、D 的pk

注意：使用字符串值C，D我想使用灵活的搜索查询获取产品C，D以及A，B的pk

详细信息：

我列出了一些产品的清单。

在每个产品中都有一个名为"X"的属性，其中包含字符串类型的产品ID代码。

注："产品ID代码"是指产品列表中另一个产品的"产品ID"。

现在我想根据产品ID代码获得产品的pk？

Answer 1

我还不完全理解，但你可以试试这个

select {p1.pk},{p2.pk} from {product as p1},{product as p2} WHERE {p1.Attribute1} = {p2.code}

你可以在中添加过滤器

AND {p1.Attribute1} in ('C','D')

---

使用UNION

SELECT uniontable.PK FROM
(
{{
SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
WHERE {p1.code} = {p2.Attribute1}
}}
UNION ALL
{{
SELECT {p:PK} AS PK FROM {Product AS p}
WHERE {p1.Attribute1} is not empty
}}
) uniontable

带过滤器

SELECT uniontable.PK FROM
(
{{
SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
WHERE {p1.code} = {p2.Attribute1} AND {p2.Attribute1} in ('C','D')
}}
UNION ALL
{{
SELECT {p:PK} AS PK FROM {Product AS p}
WHERE {p.Attribute1} in ('C','D')
}}
) uniontable