MySql:从其他表加入并计算结果

Question

我希望标题不会太困惑 - 我不知道如何调用它。

假设您有一张产品表:

+----+---------------------------+-------+
| id | name                      | stock |
+----+---------------------------+-------+
|  1 | 1. Product of set         |     2 |
|  2 | 2. Product of set         |     5 |
|  3 | 3. Product of set         |     3 |
|  4 | Set of Product 1, 2 and 3 |     0 |
+----+---------------------------+-------+

产品 1-3 可以单独销售，也可以成套销售(即产品 4)。所以我们有另一个表:

+---------+-------------+-------+
| product | set_product | count |
+---------+-------------+-------+
|       1 |           4 |     1 |
|       2 |           4 |     2 |
|       3 |           4 |     1 |
+---------+-------------+-------+

...表示产品 4 由 1x 产品 1、2x 产品 2 和 1x 产品 3 组成。

在产品表中有一列“stock”，表示每种产品当前有多少库存。

问题是:如何通过一个SELECT获取产品4的库存(当然这取决于产品1-3的库存)？

---

为了设置测试用例，我创建了这段代码:

CREATE TABLE products (
    `id` int unsigned NOT NULL AUTO_INCREMENT, unique key(id),
    `name` TINYTEXT,
    `stock` int NOT NULL DEFAULT 0 
);

CREATE TABLE sets (
    `product` int unsigned NOT NULL,
    `set_product` int unsigned NOT NULL,
    `count` int NOT NULL
);

INSERT INTO products SET id=1, name="1. Product of set", stock=2;
INSERT INTO products SET id=2, name="2. Product of set", stock=5;
INSERT INTO products SET id=3, name="3. Product of set", stock=3;
INSERT INTO products SET id=4, name="Set of Product 1, 2 and 3";

INSERT INTO sets SET product=1, set_product=4, count=1;
INSERT INTO sets SET product=2, set_product=4, count=2;
INSERT INTO sets SET product=3, set_product=4, count=1;

Answer 1

试试这个

select p.name, ifnull(min(ps.stock / s.count), p.stock) as stock
from products as p
    left outer join sets as s on s.set_product = p.id
    left outer join products as ps on ps.id = s.product
group by p.name

SQL FIDDLE
你也可以做联合解决方案

select p.id, p.name, min(ps.stock / s.count) as stock
from products as p
    inner join sets as s on s.set_product = p.id
    inner join products as ps on ps.id = s.product
group by p.id, p.name

union all

select p.id, p.name, p.stock
from products as p
where p.id not in (select distinct tt.set_product from sets as tt)

SQL FIDDLE