MySQL 对连接表应用过滤器

Question

假设我们有一个包含物理长方体的数据库，定义为对象。每个物体都有长度、宽度和高度。如下例所示。

CREATE TABLE dimensions(
    id int PRIMARY KEY NOT NULL,
    value int
);

CREATE TABLE object(
    id int,
    dimension VARCHAR(100),
    dimension_value_id int,
    FOREIGN KEY (dimension_value_id) REFERENCES dimensions(id)
);

INSERT INTO dimensions VALUES(0, 100);
INSERT INTO dimensions VALUES(1, 200);
INSERT INTO dimensions VALUES(2, 300);
INSERT INTO dimensions VALUES(3, 400);

INSERT INTO object VALUES(0, "length", 1);
INSERT INTO object VALUES(0, "width", 3);
INSERT INTO object VALUES(0, "height", 2);

INSERT INTO object VALUES(1, "length", 1);
INSERT INTO object VALUES(1, "width", 1);
INSERT INTO object VALUES(1, "height", 2);

我的目标是过滤出尺寸小于 251x251x350 的对象，在示例中，这将是 id 为 1 的对象。

加入表时我得到记录:

SELECT * FROM object o
JOIN dimensions d ON o.dimension_value_id = d.id

但是现在如何应用过滤器。我遇到了存在/不存在的解决方案。但在这种情况下，这是行不通的，因为我需要有一个独占不存在。如何实现这一目标？

Answer 1

您必须将 3 个维度中较小的一个与 3 个值中较小的一个进行比较，将最大维度与 3 个值中最大的一个进行比较，将中间一个与 3 个值中的中间一个进行比较:

select o.id 
from object o inner join dimensions d 
on o.dimension_value_id = d.id
group by o.id 
having 
  min(d.value) < least(251, 251, 350) 
  and 
  max(d.value) < greatest(251, 251, 350)
  and
  max(case when o.dimension = 'length' then d.value end) + 
  max(case when o.dimension = 'width' then d.value end) +
  max(case when o.dimension = 'height' then d.value end) -
  (min(d.value) + max(d.value)) < 
  (251 + 251 + 350) - (least(251, 251, 350) + greatest(251, 251, 350))

请参阅demo .
用于传递 251, 251, 350 等参数的简化版本:

select o.id 
from object o inner join dimensions d 
on o.dimension_value_id = d.id
cross join (select 251 as col1, 251 as col2, 350 as col3) t
group by o.id 
having 
  min(d.value) < least(max(t.col1), max(t.col2), max(t.col3)) 
  and 
  max(d.value) < greatest(max(t.col1), max(t.col2), max(t.col3))
  and
  max(case when o.dimension = 'length' then d.value end) + 
  max(case when o.dimension = 'width' then d.value end) +
  max(case when o.dimension = 'height' then d.value end) -
  (min(d.value) + max(d.value)) < 
  (max(t.col1) + max(t.col2) + max(t.col3)) - (
    least(max(t.col1), max(t.col2), max(t.col3)) + 
    greatest(max(t.col1), max(t.col2), max(t.col3))
  )

请参阅demo .