从 A 中选择全部并连接 B 中的所有非重复组合,以便在 MySQL 中: ab、ac、ad、abc、abd...?
我有一张 A 桌子,里面有食品。
我有一张带有食物插件的表B。
表B具有字段:
- 价格
- 添加_卡路里
- 名称
- 产品
我想要进行这样的查询,该查询将获得产品和插件的前10个(或更多)组合,从而产生最佳卡路里 / 价格比率。我需要保留要向用户显示的插件和产品的名称。
我应该能够在 1 个产品上安装多个插件,这样我就应该尝试并运行所有组合。
如果我们有插件 a、b、c、d 那么我必须尝试组合:
- Product + a
- Product + b
- Product + ab
- Product + ac
- Product + ad
- Product + bc
- Product + bd
- ...
并非每个产品都可以选择每个插件。还有一个附加的“连接器”表,指示产品支持的插件。
I have a table A with food products.
I have a table B with food addons.
Table B has the fields:
- price
- added_calories
- name
- products
I want to make such query that will get the top 10 (or more) combinations of product and addons that will result the best calories / price ratio . I need to keep the names of the addons and product to display to the user.
I should be able to have several addons on 1 product so that I should try and run through all combinations.
If we have addons a, b, c, d then I have to try the combinations:
- Product + a
- Product + b
- Product + ab
- Product + ac
- Product + ad
- Product + bc
- Product + bd
- ...
Not every addon can be selected for each product. There's an additional 'connector' table which indicates supported addons for a product.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
假设您想最大化卡路里/价格,我认为这是可行的。诀窍在于,对于给定的特定产品,您可以通过按卡路里/价格比降序堆积附加组件来最大化卡路里/价格,直到总比率开始下降。
以下查询可能效率低得可笑,需要认真优化,但至少它表明存在解决方案:
编辑: 好的,我调试了它,现在它实际上可以工作了(!)。以下是一些测试数据:
和结果:
编辑 2: 哎呀,我让它最大化每卡路里的价格,而不是每卡路里的价格。固定的。
编辑3:子查询忽略了连接器表。我(希望)已经修复了该错误,但我还无法对其进行测试。
Assuming that you want to maximize calories / price, I think this is doable. The trick is that, given a specific product, you'll maximize calories / price by piling on add-ons, in descending order by calories / price ratio, until the total ratio starts to decrease.
The following query is probably ridiculously inefficient and in need of serious optimization, but at least it shows that a solution exists:
Edit: OK, I debugged it, now it actually works(!). Here's some test data:
And results:
Edit 2: Oops, I had it maximizing price per calories instead of calories per price. Fixed.
Edit 3: The subqueries were ignoring the connector table. I've (hopefully) fixed that bug, but I haven't been able to test it yet.