经过几个小时的组合和尝试，我终于做到了：

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来完成这一点。

我提出了一个替代的，可能更容易理解的方法。让我们在一个简单的例子中一步一步地看这个问题。我们的初始表可以有以下数据：

• objects* table：

object_id  |  object_group_id
    1                1
    2                1
    3                1
    4                2
    5                1
    6                1

字符串

• attributes* table：

attr_id | attr_object_id  |  attr_property_id  |  attr_value
    1           1                   1                50000
    2           1                   1                75000
    3           1                   1               150000
    4           1                   2                 1000
    5           1                   2                 5000
    6           2                   1                30000
    7           2                   1               200000
    8           2                   2                 7000
    9           3                   1               500000
   10           3                   2                 1000
   11           4                   1                90000
   12           4                   2                 6000
   13           5                   1               150000
   14           5                   2                 3000
   15           6                   1                70000
   16           6                   2                 1000

型
I.我们开始处理第二个表，因为问题的主要部分实际上在其中，我们直接应用我们的过滤器：

SELECT * from attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)

型
请注意，我们在条件之间使用“OR”，因为我们需要从 attributes 表中获取适用于 * 一个 * OR * 另一个 * 条件的所有行
结果如下：

attr_id | attr_object_id  |  attr_property_id  |  attr_value
    1           1                   1                50000
    2           1                   1                75000
    5           1                   2                 5000
    6           2                   1                30000
    8           2                   2                 7000
   11           4                   1                90000
   12           4                   2                 6000
   14           5                   2                 3000
   15           6                   1                70000

型
II.现在我们只需要取上面结果中 attr_property_id 为“1”和“2”的那些 * attr_object_id*，即那些 attr_object_id 符合我们最初问题的过滤器。我们可以通过以下查询来实现：

SELECT attr_object_id, count(distinct(attr_property_id)) FROM attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)
GROUP BY attr_object_id

型
其结果是：

attr_object_id  |  count
      1              2
      2              2
      4              2
      5              1
      6              1

型
从上面的结果可以看出，attr_object_id 中的“1”、“2”和“4”都满足我们最初问题的过滤器，但“5”和“6”只是其中一个过滤器。现在让我们过滤掉“5”和“6”：

SELECT attr_object_id FROM attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)
GROUP BY attr_object_id
HAVING count(distinct(attr_property_id)) = 2

型
我们得到：

attr_object_id
      1
      2
      4

型
III.至此，问题的主要部分已经解决，我们只需要从 objects 表中应用过滤器，即 object_group_id = 1。这一个的查询很简单，我们只需要INNER JOIN objects 和 attributes 表，并在WHERE子句中添加一个条件：

SELECT attr_object_id FROM attributes
INNER JOIN objects on attributes.attr_object_id = objects.object_id
WHERE object_group_id = 1 AND ((attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000))
GROUP BY attr_object_id
HAVING count(distinct(attr_property_id)) = 2

型
我们的例子的最终结果是：

attr_object_id
      1
      2

型

试试这个，我不知道你为什么会有最后几句

SELECT
  o.object_id, o.object_group_id,
  f1.attr_value AS val1,
  f2.attr_value AS val2,
FROM objects AS o
LEFT JOIN attributes f1 ON o.object_id = f1.attr_object_id AND f1.attr_property_id = 1
LEFT JOIN attributes f1 ON o.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';

字符串
删除此行并测试它

AND
   f1.attr_value <= '100000'
AND
   f2.attr_value > '2000';

型