sql - 如何查询表中的传递匹配？

Question

给出下表

单位：

| id | singular | plural |
|----|----------|--------|
| 3  | onion    | onions |
| 4  | bag      | bags   |
| 5  | gram     | grams  |
| 6  | ml       | ml     |
| 7  | mm       | mm     |

和

转换：

| id | convert_from | convert_to | factor |
|----|--------------|------------|--------|
| 3  | 4            | 3          | 5      |
| 4  | 3            | 5          | 125    |

我如何从（例如）bag（单位4）获得所有可能的转换因子？

我希望答案类似于表格

| convert_from | convert_to | factor |
|--------------|------------|--------|
| 4            | 3          | 5      |
| 4            | 5          | 625    |

注意事项：

1. 无法保证单位可能出现在转换表 ( convert_from, ) 的哪一列。convert_to

2. 通过单位5、6或的转换7应该被忽略。

   也就是说， 1->2->4->5有效，1->2->4->5->7无效。

3. 一个SQL解决方案（或重新构建数据库以促进 SQL 解决方案）将是理想的，但也将赞赏进行多个 SQL 查询的代码解决方案。
4. 如果单位表中的其他单位不构成转换图的一部分（或者如果它们通过无效转换（、、或）构成分支的一部分），则应忽略5这些6单位7。这是一个简化的视图。

说明性示例

暂时忽略 SQL 并检索数据，这就是我想要实现的目标：

我想建立一个用户可以存储家用产品的系统。一个产品有一个与之关联的单位。单位可能是 SI 单位，例如 mm、ml、g..，也可能是离散单位，例如洋葱或罐头。

单位之间可以有关系，例如1 can -> 330 ml. 我的问题的复杂性来自这样一个事实，即单个单元的转换可能分布在许多产品中。

再次考虑这个can例子，我们可以有一个pepsi (crate of 24)单位为的crate产品，以及另一个pepsi (can)单位为 的产品can。

当用户创建pepsi (can)产品时，他们提供以下转换：

1 can -> 330 ml

之后，用户创建pepsi (crate of 24)产品，并提供以下转换：

1 crate -> 24 can

最后，用户问“我有多少百事可乐？”

我希望能够回答：

• 25罐
• 1.0417 个板条箱
• 8250 毫升。

但是，我不知道如何将 crates 转换为 ml。

这是另一个图解形式的例子：

编辑：

1. 将 mms 和 mls 更改为 mm 和 ml。不知道我在想什么……
2. 添加了图表以帮助阐明我在寻找什么而不是解决方案。

标签： sqlpostgresql

Answer 1

假设数据中没有循环，您可以使用递归 CTE。

我添加了一个额外的is_terminal列来标识您不想再转换的终端单元（5、6 和 7）。查询是：

with recursive
e (convert_from, convert_to, factor, is_terminal) as (
  select id, id, 1, is_terminal from units where id = 4 -- bag
 union all
  select e.convert_from, c.convert_to, e.factor * c.factor, u.is_terminal
  from e
  join conversions c on c.convert_from = e.convert_to
  join units u on u.id = c.convert_to
  where not e.is_terminal
)
select * from e where convert_from <> convert_to

结果：

convert_from  convert_to  factor  is_terminal
------------  ----------  ------  -----------
4             3           5       false
4             5           625     true

请参阅DB Fiddle上的运行示例。这是我用来测试的数据脚本：

create table units (
  id int,
  is_terminal boolean
);

insert into units (id, is_terminal) values
  (3, false), (4, false),
  (5, true), (6, true), (7, true);

create table conversions (
  id int,
  convert_from int,
  convert_to int,
  factor int
);

insert into conversions (id, convert_from, convert_to, factor) values
  (3, 4, 3, 5),
  (4, 3, 5, 125);