在MySQL中如何搜索分组列？

Question

我需要创建一个男士的数据库，每个男士可以有一个或多个属性，每个男士的属性都有一个特定的值，听起来很容易吧？但问题变得有点不可能（已经处理了5天:s）。

所以我创建了这3个表：

CREATE TABLE guy (
  id int(11),
  name varchar(255)
);

CREATE TABLE attribute (
  id int(11),
  name varchar(255)
);

-- each value references one guy and one attribute
CREATE TABLE _value (
  id int(11),
  guy_id int(11),
  attribute_id int(11),
  _value varchar(255)
);

使用此示例数据：

INSERT INTO attribute VALUES (1, 'age'), (2, 'dollars'), (3, 'candies');
INSERT INTO guy VALUES (1, 'John'), (2, 'Bob');
INSERT INTO _value VALUES (1, 1, 1, 12), (2, 1, 2, 15), (3, 1, 3, 3);
INSERT INTO _value VALUES (4, 2, 1, 15), (5, 2, 2, 20), (6, 2, 3, 6);

并创建此查询：

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id;

这给了我这个结果：

+------+-----------+-------+
| guy  | attribute | value |
+------+-----------+-------+
| John | age       | 12    |
| John | dollars   | 15    |
| John | candies   | 3     |
| Bob  | age       | 15    |
| Bob  | dollars   | 20    |
| Bob  | candies   | 6     |
+------+-----------+-------+

这才是真正的问题：

后来，我的老板告诉我他想使用尽可能多的条件来过滤数据，并能够用“and”和“or”对这些条件进行分组。例如，他可能想要执行以下疯狂的条件：

获取年龄大于10岁、少于18美元、有2个以上糖果但不超过10个糖果的人，但无论如何，也要包括年龄恰好为15岁的人。 这将转换为以下过滤器：

-- should return both John and Bob
(age > 10 and dollars < 18 and candies > 2 and candies < 10) or (age = 15)

我创建筛选器没有问题（我使用jqgrid来完成），问题在于属性并不是列，而是行，因此我不知道如何将查询与筛选器混合使用，我试过这样做：

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id
GROUP BY guy
HAVING (
    (attribute = 'age' and value > 10) AND
    (attribute = 'dollars' and value < 18) AND
    (attribute = 'candies' and value > 2) AND
    (attribute = 'candies' and value < 10)
       )
OR
       (
     (attribute = 'age' and value = 15)
       )

但是只返回了Bob :( 我应该得到John和Bob两个人。

那么，我该如何混合筛选器和查询呢？

请记住，每个人拥有的属性数量都是相同的，但随时可以添加更多的属性和人员，例如，如果我想添加一个名为'Mario'的人，我会这样做：

-- we insert the guy Mario
INSERT INTO guy VALUES (3, 'Mario');
-- with age = 5, dollars = 100 and candies = 1
INSERT INTO _value VALUES (7, 3, 1, 5), (8, 3, 2, 100), (9, 3, 3, 1);

如果我想创建属性“apples”，我会这样做：

-- we insert the attribute apples
INSERT INTO attribute VALUES (4, 'apples');
-- we create a value for each guy's new attribute, John as 7 apples, Bob has 3 and Mario has 8
INSERT INTO _value VALUES (10, 1, 4, 7), (11, 2, 4, 2), (12, 3, 4, 8);

现在我应该能够在我的查询中包含关于苹果的条件。

我希望我表达清楚了，感谢您花费的时间 :)

注意：也许如果有一种方法可以将每个人的所有属性放在一行中？像这样：

+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+
| guy  | attribute | value | guy  | attribute  | value  | guy  | attribute  | value  | guy  | attribute  | value  |
+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+
| John | age       |    12 | John | dollars    |     15 | John | candies    |      3 | John | apples     |      7 |
| Bob  | age       |    15 | Bob  | dollars    |     20 | Bob  | candies    |      6 | Bob  | apples     |      2 |
| Mario| age       |    5  | Mario| dollars    |     100| Mario| candies    |      1 | Mario| apples     |      8 |
+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+

注意2：@iim建议（在这个问题中：如何在MySQL中搜索分组列？（如果可能的话也可以在Hibernate中））对于每个属性都可以进行自连接，是的，这可能会解决问题，但当人们有大量属性（如30个或更多）时，可能会出现性能问题。

注意3：我不能改变数据库模式:(

- ilovelamp

Answer 1

这个怎么样？

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v1 ON g.id = v1.guy_id 
  JOIN attribute a1 ON a1.id = v1.attribute_id
JOIN _value v2 ON g.id = v2.guy_id 
  JOIN attribute a2 ON a2.id = v2.attribute_id
JOIN _value v3 ON g.id = v3.guy_id 
  JOIN attribute a3 ON a3.id = v3.attribute_id
JOIN _value v4 ON g.id = v4.guy_id 
  JOIN attribute a4 ON a4.id = v4.attribute_id
JOIN _value v5 ON g.id = v5.guy_id 
  JOIN attribute a5 ON a5.id = v5.attribute_id
WHERE (
    (a1 = 'age' and v1 > 10) AND
    (a2 = 'dollars' and v2 < 18) AND
    (a3 = 'candies' and v3 > 2) AND
    (a4 = 'candies' and v4 < 10)
  ) OR (a5 = 'age' and v5 = 15)

编辑：修复了一些愚蠢的错误：

SELECT DISTINCT g.id, g.name 'guy'
FROM guy g 
JOIN _value v1 ON g.id = v1.guy_id 
  JOIN attribute a1 ON a1.id = v1.attribute_id
JOIN _value v2 ON g.id = v2.guy_id 
  JOIN attribute a2 ON a2.id = v2.attribute_id
JOIN _value v3 ON g.id = v3.guy_id 
  JOIN attribute a3 ON a3.id = v3.attribute_id
JOIN _value v4 ON g.id = v4.guy_id 
  JOIN attribute a4 ON a4.id = v4.attribute_id
JOIN _value v5 ON g.id = v5.guy_id 
  JOIN attribute a5 ON a5.id = v5.attribute_id
WHERE (
    (a1.name = 'age' and v1._value > 10) AND
    (a2.name = 'dollars' and v2._value < 18) AND
    (a3.name = 'candies' and v3._value > 2) AND
    (a4.name = 'candies' and v4._value < 10)
  ) OR (a5.name = 'age' and v5._value = 15)

具体来说，我忘记了WHERE子句中的字段名称，只选择“guy”字段，并添加了DISTINCT以仅获取每个人的一行数据。

- Javier

---

@rhinojosa 是的，绝对没问题。或者你可以将每个连接都改成INNER JOIN，以创建只有一行的结果集，而且所有内容都已经在那里了。试试这个方法。或者你可以创建其他变量来保存其他查询，就像一个变量用于保存苹果的价值，另一个变量用于保存年龄，这样每个人只会显示一行。 - Nathan

@rhinojosa：请查看修改内容，现在它仅显示人的身份，因此每个人应只有一行。要获取该人的所有数据，我会进行第二个查询，这样您就可以针对搜索和显示进行优化。我想它可以添加而不破坏结构；但是调试会非常困难。 - Javier

请注意，最好将“attribute”表删除，因为它只是为属性命名而存在；但是您的编译器可以读取“id”，并使用“v1.attribute_id = 3”而不是“a1.name ='apple'”。 - Javier

@Javier，谢谢Javier：），是的，我进行了一些更改条件值的测试，效果非常好：），我真的很喜欢性能部分，这就像优先级一样重要，感谢您的建议，现在已经很晚了，也许我应该回家了（晚上10:30还在办公室:s），我保证明天早上会进行广泛的测试：），我认为你的答案和muistooshort的答案都非常有前途，你觉得我们可以将它们混合起来，或者可能改进查询吗？再次感谢您的时间：）祝您度过愉快的一天。 - ilovelamp

@Javie，是的，你完全正确，应该排除属性表，我会使用jqgrid通过它们的ID而不是名称来获取属性。 - ilovelamp

Answer 2

可能有类似这样的选择：

select g.name as guy
from guy g
join _value v on g.id = v.guy_id
join attribute a on a.id = v.attribute_id
where (a.name = 'age'     and v._value > 10)
   or (a.name = 'dollars' and v._value < 18)
   or (a.name = 'candies' and v._value > 2)
group by g.name
having count(*) = 3

union

select g.name as guy
from guy g
join _value v on g.id = v.guy_id
join attribute a on a.id = v.attribute_id
 where (a.name = 'age' and v._value = 15)
group by g.name       -- These two clauses are not necessary,
having count(*) = 1   -- they're just her for symmetry

你可以将外部的“或”条件转换为 UNION，而“与”条件则可以使用通常的“having count(*) 匹配条件数量”的方式处理。

我不知道这种方法是否适用于你老板想让你做的所有事情，但也许它会有所帮助。

- mu is too short

---

这看起来很有前途：），您认为如果人们开始拥有大量属性（如30个或更多），而我最终拥有10万个或更多人，会有任何性能问题吗？ - ilovelamp

@rhinojosa：我猜限制问题可能是有多少条件超过了人数和属性的数量（当然，假设你已经正确地建立了索引）。 - mu is too short

我认为你和Javier的回答都很有前途，你觉得我们能不能把它们混合起来，或许改进一下查询呢？我可能要到明天才能回答，现在已经很晚了，我需要睡觉，对此很抱歉:s，祝你有美好的一天 :) - ilovelamp

Answer 3

如果问题是“属性不是列，而是行”，那么怎么样考虑使用视图。你不能更改数据库模式，但可以考虑创建一个视图，如下所示：

CREATE VIEW the_attributes as 
  select a.id, a.name as attribute_name, v._value
  from attribute a JOIN value v
  ON v.attribute_id = a.id

从这个开始可能会更好。

然后我认为你应该能够做到：

select guy.id from guy JOIN the_attributes ON the_attributes.guy_id = guy.id
where 
the_attributes.name = 'age' and _value > 10 and
the_attributes.name = 'dollar' and _value < 18 and
the_attributes.name = 'candies' and _value > 2 and
the_attributes.name = 'candies' and _value <10 ) or
the_attributes.name = 'age' and _value = 15 ) 

无论这些最终是否对您有所帮助，您必须自行判断，但这是我最初阅读问题时想到的。肯定看起来可读 ;(

- Michael Durrant

---

谢谢回复 :)，我无法运行您的SQL语句，您是不是想写这个：codeCREATE VIEW the_attributes as select a.id, v._value from attribute a JOIN _value v ON v.attribute_id = a.idcode。您能给我一些提示吗？我仍然有值和属性作为行，我需要自连接视图或类似的东西吗？ - ilovelamp

Answer 4

以下内容将帮助您使条件更加简单明了，但我不能保证在具有30多个属性的100,000多个对象中效率非常高。这需要您自己亲身体验。

SELECT g.name guy, a.name attribute, v._value value
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id
GROUP BY guy
HAVING (
    SUM(a.name = 'age'     and v._value > 10) = 1 AND
    SUM(a.name = 'dollars' and v._value < 18) = 1 AND
    SUM(a.name = 'candies' and v._value > 2 ) = 1 AND
    SUM(a.name = 'candies' and v._value < 10) = 1
       )
OR
       (
    SUM(a.name = 'age'     and v._value = 15) = 1
       )

（我在这里假设一个人不能有重复的属性。）

- Andriy M

---

这个完美地运作了！非常整洁简单的答案，我用各种疯狂的查询测试过它，在每种情况下似乎都能正常工作，我想我会采用你的答案 :) - ilovelamp

Answer 5

试试这个，也许会有帮助。

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id
WHERE a.ID = v.attribute_ID
      AND v._value = 'values you want'
      AND  NOT v._value = 'values you don''t want'

如果您需要其他任何帮助，请告诉我。

- Nathan

---

谢谢回复：），这有什么不同吗？：codeWHERE a.ID = v.attribute_ID;code，你能帮我把这些条件包含在你的查询中吗？ code(age > 10 and dollars < 18 and candies > 2 and candies < 10) or (age = 15)code，抱歉我自己不会做，我不确定怎么做:s - ilovelamp