Mysql交集中的单列多过滤器
这是一个表格
CarID| Attribute | Value
1 | Color | Red
2 | Color | Blue
3 | Color | Red
1 | Type | Coupe
2 | Type | Hatch Back
3 | Type | Coupe
3 | Make | Honda
2 | Make | Toyota
1 | Make | Ford
现在我想运行一个过滤器,例如 Select * From Cars WHERE (Attribute = Color AND Value = Red) AND (Attribute = Make AND Value = Honda).... 并希望获得 CarID 为 3 !
这是两个查询的交集的简单情况,但我不知道如何在单个查询中完成它。
任何帮助都适用。
Here is a table
CarID| Attribute | Value
1 | Color | Red
2 | Color | Blue
3 | Color | Red
1 | Type | Coupe
2 | Type | Hatch Back
3 | Type | Coupe
3 | Make | Honda
2 | Make | Toyota
1 | Make | Ford
Now I would like to run a filter Like Select * From Cars WHERE (Attribute = Color AND Value = Red) AND (Attribute = Make AND Value = Honda).... and Hope to get the CarID as 3 !
This is simple case of Intersection of 2 queries but I don't know how to get it done in a single query.
Any help appriciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
你可能不得不改变...
you may have to change the having to...
最直接的解决方案是使用子查询为每个属性模拟一个单独的表(尽管它可能不是最有效的):
The most straightforward solution would be to simulate a separate table for each attribute with sub queries (although it might not be the most efficient):
这是查询的另一个变体
在 (carid, Attribute) 上建立索引将会创造奇迹。
Here is another variant of the query
Having an index on (carid, Attribute) will do wonders.