我需要创建一个男士的数据库,每个男士可以有一个或多个属性,每个男士的属性都有一个特定的值,听起来很容易吧?但问题变得有点不可能(已经处理了5天:s)。
所以我创建了这3个表:
使用此示例数据:
并创建此查询:
这给了我这个结果:
这才是真正的问题:
后来,我的老板告诉我他想使用尽可能多的条件来过滤数据,并能够用“and”和“or”对这些条件进行分组。例如,他可能想要执行以下疯狂的条件:
获取年龄大于10岁、少于18美元、有2个以上糖果但不超过10个糖果的人,但无论如何,也要包括年龄恰好为15岁的人。 这将转换为以下过滤器:
我创建筛选器没有问题(我使用jqgrid来完成),问题在于属性并不是列,而是行,因此我不知道如何将查询与筛选器混合使用,我试过这样做:
但是只返回了Bob :( 我应该得到John和Bob两个人。
那么,我该如何混合筛选器和查询呢?
请记住,每个人拥有的属性数量都是相同的,但随时可以添加更多的属性和人员,例如,如果我想添加一个名为'Mario'的人,我会这样做:
如果我想创建属性“apples”,我会这样做:
现在我应该能够在我的查询中包含关于苹果的条件。
我希望我表达清楚了,感谢您花费的时间 :)
注意:也许如果有一种方法可以将每个人的所有属性放在一行中?像这样:
注意2:@iim建议(在这个问题中:如何在MySQL中搜索分组列?(如果可能的话也可以在Hibernate中))对于每个属性都可以进行自连接,是的,这可能会解决问题,但当人们有大量属性(如30个或更多)时,可能会出现性能问题。
注意3:我不能改变数据库模式:(
所以我创建了这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:我不能改变数据库模式:(