表 我有:
- 产品(编号、标题(
[1, "旧车"], [2, "新车"],[3, "未来车"](
- 属性(id、名称(
型号"], [3, "颜色"]
- >Attribute_values (attribute_id, product_id, 值(
[1, 1, "丰田"], [1, 2, "本田"], [1, 3, "福特"], [1, 3, "红色"], [2, 3, "白色"], [3,3, "红色"]
我尝试查询:
select p.*, a.name as attribute_name, av.value as attribute_value from attributes as a
join attribute_values as av on a.id=av.attribute_id
join products as p on av.product_id=p.id
where
((a.name='brand' and av.value='toyota') or (a.name='color' and av.value='red'))
它提供与 or 查询条件匹配的每一行。
例如:
- [id:1, 标题:"旧车", attribute_name:"品牌", attribute_value:"丰田"]
- [id:1, 标题:"旧车", attribute_name:"颜色", attribute_value:"红色"]
- [id:3, 标题:"未来汽车", attribute_name:"颜色", attribute_value:"红色"]
,但我需要"旧车"而不是"未来车"。
这可以通过单个查询完成吗?
你可以试试这个查询。当记录与 1 个条件匹配时,原始查询输出 1 行。 从结果中,我们检查相同的product id
结果中是否有 2 条记录。如果是,则表示 2 个标准(品牌 =Toyota
和颜色 =red
(匹配相同的product
。
SELECT id as product_id, count(attribute_name) as attribute_count FROM
(
select
p.*,
a.name as attribute_name,
av.value as attribute_value
from products as p
join attribute_values as av
on p.id = av.product_id
join attributes as a
on a.id = av.attribute_id
where
(a.name='brand' and av.value='toyota')
or (a.name='color' and av.value='red')
) a
GROUP BY a.id
HAVING (attribute_count = 2);
用于测试的 SQL 小提琴链接:http://sqlfiddle.com/#!9/ab857a/2
编辑:
另一方面,您也可以尝试此second approach
。首先,我们执行 2 个单独的subselect
,以获得符合 2 个标准中每个标准的产品。然后,我们将子选择JOIN
在一起,以获得符合两个条件的产品:
SELECT
a.*,
b.attribute_2,
b.value_2
FROM
(
SELECT
p.*,
a.name as attribute_1,
av.value as value_1
FROM
products as p
JOIN attribute_values as av
ON av.product_id = p.id
JOIN attributes as a
ON a.id = av.attribute_id
WHERE
a.name = 'brand'
and av.value = 'toyota'
) a
JOIN
(
SELECT
p.*,
a.name as attribute_2,
av.value as value_2
FROM
products as p
JOIN attribute_values as av
ON av.product_id = p.id
JOIN attributes as a
ON a.id = av.attribute_id
WHERE
a.name = 'color'
and av.value = 'red'
) b
ON a.id = b.id
SQL 小提琴代码示例:http://sqlfiddle.com/#!9/8b836d/1
假设这是正确的,我已经更改了attribute_value表中的记录,如下所示:
attribute_id | product_id | value
------------------------------------------
1 | 1 | toyota
1 | 2 | honda
1 | 3 | ford
3 | 1 | red
3 | 2 | white
3 | 3 | red
我已经修改了您的查询,如下所示:
select p.*, a.name as attribute_name, av.value as attribute_value from attributes as a
join attribute_values as av on a.id=av.attribute_id
join products as p on av.product_id=p.id
where
(
if(a.name='brand',av.value='toyota',0)
or if(a.name='color',av.value='red',0)
)
GROUP BY p.id HAVING COUNT(av.attribute_id) =2
这里的"2">应该是动态的,具体取决于您过滤的属性数量。由于在您的示例中您使用 2 个属性进行了过滤,因此我提到了 2
个小提琴出现在: http://sqlfiddle.com/#!9/b6ff9/2