mysql - 在 MySQL 中创建 INTERSECT 查询

Question

我有一张 table

ppv(产品 ID | 属性 ID | 值 ID)

和

v(id | property_id | 值)

我尝试进行查询以获取产品，其中:

select product_id from v, ppv as t INNER JOIN ppv as t2 
ON t.product_id = t2.product_id and t1.property_id = t2.property_id
where
( t1.property_id = 1 and v.value > $a and v.value < $b )
and
( t2.property_id = 2 and v.value > $c and v.value < $d )

但是还没有任何结果。请帮忙。

所以，

ppv (product_id | property_id | value_id)
 1              1            50
 1              2            2
 1              3            3
 2              1            28
 2              2            2
 2              3            29

下一个表格

v( id | property_id | value)
 2          2           3
 3          3           2
 28         1           2600000
 29         3           3
 50         1           2500000

<小时/>

** 我通过这种方式找到了解决这个问题的方法 **

SELECT `product_id`, 
       CONVERT(GROUP_CONCAT(v.`property_id`) USING cp1251) as properties
FROM `values` as v INNER JOIN `product_property_values` as ppv
ON ppv.value_id = v.id
WHERE
   (v.`property_id` = 1 and v.value >= 2500000 and v.value <= 3000000 ) 
   or 
   (v.`property_id` = 2 and v.value >= 1 and v.value <= 3 )
GROUP by product_id
HAVING LOCATE('1', properties) and LOCATE('2', properties)

结果:

测试 1

v.`property_id` = 1 and v.value >= 2500000 and v.value < 3000000
v.`property_id` = 2 and v.value >= 1 and v.value <= 2

 product_id properties
 Empty

测试2

v.`property_id` = 1 and v.value >= 2550000 and v.value < 3000000
v.`property_id` = 2 and v.value >= 1 and v.value <= 4

product_id  properties
     2          2,1

测试3

v.`property_id` = 1 and v.value >= 2000000 and v.value < 3000000
v.`property_id` = 2 and v.value >= 1 and v.value <= 4

product_id  properties
     1          1,2
     2          2,1

没关系!

Answer 1

您似乎并未真正加入表v。另外，我认为没有必要链接到 ppv 两次。尝试:

SELECT ppv.product_id
FROM v
INNER JOIN ppv ON v.property_id = ppv.property_id
WHERE
(ppv.property_id = 1 and v.value > $a and v.value < $b )
OR
(ppv.property_id = 2 and v.value > $c and v.value < $d )