从 A 中选择全部并连接 B 中的所有非重复组合，以便在 MySQL 中： ab、ac、ad、abc、abd...？

Question

我有一张 A 桌子，里面有食品。

我有一张带有食物插件的表B。

表B具有字段：

• 价格
• 添加_卡路里
• 名称
• 产品

我想要进行这样的查询，该查询将获得产品和插件的前10个（或更多）组合，从而产生最佳卡路里 / 价格比率。我需要保留要向用户显示的插件和产品的名称。

我应该能够在 1 个产品上安装多个插件，这样我就应该尝试并运行所有组合。

如果我们有插件 a、b、c、d 那么我必须尝试组合：

• Product + a
• Product + b
• Product + ab
• Product + ac
• Product + ad
• Product + bc
• Product + bd
• ...

并非每个产品都可以选择每个插件。还有一个附加的“连接器”表，指示产品支持的插件。

Answer 1

假设您想最大化卡路里/价格，我认为这是可行的。诀窍在于，对于给定的特定产品，您可以通过按卡路里/价格比降序堆积附加组件来最大化卡路里/价格，直到总比率开始下降。

以下查询可能效率低得可笑，需要认真优化，但至少它表明存在解决方案：

SELECT
  p.name AS product,
  GROUP_CONCAT(a.name) AS additives,
  p.price + COALESCE(SUM(a.price), 0) AS price,
  p.calories + COALESCE(SUM(a.added_calories), 0) AS calories,
  (p.calories + COALESCE(SUM(a.added_calories), 0)) /
    (p.price + COALESCE(SUM(a.price), 0)) AS calories_per_price
FROM
  products AS p
  LEFT JOIN connector AS c ON c.product = p.id
  LEFT JOIN additives AS a
    ON a.id = c.additive
    AND (a.added_calories / a.price) > (
      (p.calories + COALESCE((
        SELECT SUM(b.added_calories) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0)) /
      (p.price + COALESCE((
        SELECT SUM(b.price) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0))
    )
GROUP BY p.id
ORDER BY calories_per_price DESC
LIMIT 10;

编辑： 好的，我调试了它，现在它实际上可以工作了（！）。以下是一些测试数据：

INSERT INTO products (id, name, calories, price) VALUES
  (1, 'Cardboard', 0, 1),
  (2, 'Lard', 1000, 100),
  (3, 'Spaghetti', 10, 50);
INSERT INTO additives (id, name, added_calories, price) VALUES
  (1, 'Salt', 0, 2),
  (2, 'Butter', 500, 100),
  (3, 'Cheese', 300, 70),
  (4, 'Pepper', 0, 3),
  (5, 'Ketchup', 50, 10),
  (6, 'Milk', 20, 10);
INSERT INTO connector (product, additive) VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5), (1,6),
  (2,1), (2,3), (2,4), (2,5),
  (3,1), (3,2), (3,3), (3,4), (3,5);

和结果：

+-----------+-----------------------+-------+----------+--------------------+
| product   | additives             | price | calories | calories_per_price |
+-----------+-----------------------+-------+----------+--------------------+
| Lard      | NULL                  |   100 |     1000 |                 10 | 
| Cardboard | Butter,Ketchup        |   111 |      550 |   4.95495495495495 | 
| Spaghetti | Butter,Cheese,Ketchup |   230 |      860 |   3.73913043478261 | 
+-----------+-----------------------+-------+----------+--------------------+

编辑 2： 哎呀，我让它最大化每卡路里的价格，而不是每卡路里的价格。固定的。

编辑3：子查询忽略了连接器表。我（希望）已经修复了该错误，但我还无法对其进行测试。