mysql - 具有 hasMany 关系的递归查询

Question

表格表示是对实际问题的简化，因为它捕获了情况但更容易理解。

两个表包含测量单位的定义。有一个包含所有单位符号的表和一个包含所有单位的定义段的关联表。例如，

• 它定义一码为0.9144米
• 它定义一条链为 22 码
• 它将一个弗隆定义为 220 码
• 它将一英亩定义为 1 链乘以 1 弗隆

表格:

measurement_units
id      name        is_base_unit
----------------------------------
1       meter       1
2       yard        0
3       chain       0
4       furlong     0
5       acre        0


measurement_unit_derivations
id      measurement_unit_id     derives_from_measurement_unit_id    factor
---------------------------------------------------------------------------
1       2                       1                                   0.9144
2       3                       2                                   22
3       4                       2                                   220
4       5                       3                                   1
5       5                       4                                   1

我正在尝试编写一个递归公用表表达式，其中每个测量单位的比率是相对于相关物理尺寸的基本单位计算的。结果应该例如包含码与米的比率以及英亩与 m^2 的比率。它应该看起来像这样:

measurement_unit_id     name        ratio
-------------------------------------------
1                       meter       1
2                       yard        0.9144
3                       chain       20.1168
4                       furlong     201.168
5                       acre        4046.8564224

问题是一个单元可以从多个其他单元派生。例如，为了找出一英亩与 m^2 的比率，我们将看到它是从一弗隆和一链导出的，而这又是从其他单位导出的。

我无法继续超越:

WITH RECURSIVE cte AS (
    SELECT 
        id AS measurement_unit_id
        name,
        1 AS ratio
    FROM measurement_units
    WHERE is_base_unit = 1
    UNION ALL
    ???
)

编辑:关于米和英亩的示例表描述方便，但也“太简单了”。我创建了另一个示例，其中递归查询也应该有效:

measurement_units
id      name        is_base_unit
----------------------------------
1       A           1
2       B           1
3       C           0
4       D           0
5       E           0

measurement_unit_derivations
id      measurement_unit_id     derives_from_measurement_unit_id    factor
---------------------------------------------------------------------------
1       3                       1                                   2
2       4                       2                                   2
3       4                       3                                   2
4       5                       4                                   2

这个结果就是目标:

measurement_unit_id     name        ratio
-------------------------------------------
1                       A           1
2                       B           1
3                       C           2
4                       D           8
5                       E           16

下面是创建这两个表及其内容的快速而肮脏的 SQL。

CREATE TABLE `measurement_units` (`id` int(10) UNSIGNED NOT NULL, `name` varchar(190) NOT NULL, `is_base_unit` tinyint(3) UNSIGNED NOT NULL);
CREATE TABLE `measurement_unit_derivations` (`id` int(10) UNSIGNED NOT NULL, `measurement_unit_id` int(10) UNSIGNED NOT NULL, `derived_from_measurement_unit_id` int(10) UNSIGNED NOT NULL, `factor` int(10) UNSIGNED NOT NULL);

INSERT INTO `measurement_units` (`id`, `name`, `is_base_unit`) VALUES (1, 'A', 1), (2, 'B', 1), (3, 'C', 0), (4, 'D', 0), (5, 'E', 0);
INSERT INTO `measurement_unit_derivations` (`id`, `measurement_unit_id`, `derived_from_measurement_unit_id`, `factor`) VALUES (1, 3, 1, 2), (2, 4, 2, 2), (3, 4, 3, 2), (4, 5, 4, 2);

Answer 1

您已正确设置递归种子。我们从 base_unit = 1 开始。UNION ALL 之后的下一部分是递归项。这是您返回 cte 并将其连接到您的源表以建立迭代关系的地方。

您的递归项将采用以下形式:

SELECT 
    mu.id,
    mu.name,
    cte.ratio * mud.factor
FROM
    cte
    INNER JOIN measurement_unit_derivations mud
        ON cte.measurement_unit_id = mud.derives_from_measurement_unit_id
    INNER JOIN measurement_units mud
        ON mud.measurement_unit_id = mu.id

这将为源自两个或多个维度的测量创建多个记录。由于规则似乎是我们将多个导出的测量值相乘以确定比率(链 * 犁沟 = 英亩)，因此我们可以在完成此递归 cte 后进行汇总。

诀窍在于没有像加法那样的乘法聚合 (SUM())。所以我们必须计算出来。我相信以下方法会起作用:

SELECT measurement_unit_id, name, EXP(SUM(LOG(ratio))) FROM cte GROUP BY measurement_unit_id, name;

把这些放在一起:

WITH RECURSIVE cte AS (
    SELECT 
        measurement_unit_id
        name,
        1 AS ratio
    FROM measurement_units
    WHERE base_unit = 1
    UNION ALL
    SELECT 
        mu.id,
        mu.name,
        cte.ratio * mud.factor
    FROM
        cte
        INNER JOIN measurement_unit_derivations mud
            ON cte.measurement_unit_id = mud.derives_from_measurement_unit_id
        INNER JOIN measurement_units mud
            ON mud.measurement_unit_id = mu.id
)
SELECT measurement_unit_id, name, EXP(SUM(LOG(ratio))) FROM cte GROUP BY measurement_unit_id, name;