我有 2 张桌子:
Table objects
:
object_id | object_group_id
Table attributes
:
attr_id | attr_object_id | attr_property_id | attr_value
现在,我想得到所有object_id
where object_group_id = 1
并过滤两个属性:
(attr_property_id = 1 AND attr_value <= '100000')
AND
(attr_property_id = 2 AND attr_value > '2000')
我试图构建一些查询,如下所示:
SELECT * FROM objects as o
/* filter1 join */
INNER JOIN
attributes AS f1
ON
o.object_id = f1.attr_object_id
AND
f1.attr_property_id = 1
/* filter2 join */
INNER JOIN
attributes AS f2
ON
f1.attr_object_id = f2.attr_object_id
AND
f2.attr_property_id = 2
WHERE
o.object_group_id = 1
AND
f1.attr_value <= '100000'
AND
f2.attr_value > '2000'
...但仍然无法得到我需要的东西。
经过几个小时的组合和尝试,我终于做到了:
SELECT * FROM objects as o
/* filter1 join */
INNER JOIN
attributes AS f1
ON
o.object_id = f1.attr_object_id
AND
f1.attr_property_id = 1
AND
f1.attr_value <= '100000'
/* filter2 join */
INNER JOIN
attributes AS f2
ON
f1.attr_object_id = f2.attr_object_id
AND
f2.attr_property_id = 2
AND
f2.attr_value > '2000'
WHERE
o.object_group_id = 1
我太接近了,并通过将所有过滤条件移至INNER JOIN
.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)