MySQL压平2个连接的递归表

Question

我有一组如下所示的表格

 equipment
 _____________________________________________________
 equipmentID | name
 _____________________________________________________
       1       widgetA  
       2       widgetB

 group
 _____________________________________________________
 fk_equipmentID | groupId | fk_parentGroupID | label
 _____________________________________________________
       1            1        null          Dimensions
       1            2        1             Overall
       1            3        2             External
       1            4        3             length
       1            5        3             width
       1            6        3             height
       1            7        null          Dimensions
       1            8        7             Overall
       1            9        8             Internal
       1           10        9             length
       1           11        9             width
       1           12        9             height
       1           13        null          color

 valueGroup
 _____________________________________________________
 fk_groupID | valueID | fk_parentVlaueID
 _____________________________________________________
     4          1           null
    null        2            1
    null        3            1
     5          4           null
     6          5           null
     10         6           null
    null        7            6
     11         8           null
     12         9           null
     13        10           null

 values
 _____________________________________________________
 fk_valueID  | id  | value
 _____________________________________________________
     2          1     5in
     3          2     12.7mm
     5          3     1 meter
     6          4     0.5m
     7          5     2in
     8          6     10in
     9          7     .25m
     10         8     blue

如何展平表格以获得如下所示的结果:

 equipmentID | name   | labels                                  | value
 _______________________________________________________________________
     1         widgetA  Dimensions, External, Overall, length     5in
     1         widgetA  Dimensions, External, Overall, length     12.7mm
     1         widgetA  Dimensions, External, Overall, width      1 meter
     1         widgetA  Dimensions, External, Overall, height     0.5m
     1         widgetA  Dimensions, External, Internal, length    2in
     1         widgetA  Dimensions, External, Internal, width     10in
     1         widgetA  Dimensions, External, Internal, height    .25m
     1         widgetA  color                                     blue

表组有 2 个外键:

• fk_equipmentID 链接到 Equipment.equipmeintID
• fk_parentGroupID 链接到 group.groupID

valueGroup 也遵循相同的概念:

• fk_groupID 链接到 group.groupID
• fk_parentValueId 链接到 valueID

对于组和值组表，条目可能有也可能没有递归条目。组表条目将始终具有至少一个与该组的最后祖先相关联的 valueGroup 条目；但可能有不止一个。

我一直在看https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161并能够使标签正确显示，但我无法取得进一步的进展。

Answer 1

如果您的表有最大深度数并且您可以保证永远不会超过它，请使用@Wanderer 的解决方案。否则，您需要每个递归值一个存储过程/函数，因为不可能以任何其他方式获取深度未知的递归数据。为了确定，请使用谷歌。因此，您的存储函数如下所示:

CREATE DEFINER=`root`@`localhost` FUNCTION `getRootValueGroup`(childCId INT, `value` BOOLEAN) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE parentCId INT;
    DECLARE currentCId INT;
    SET parentCId = childCId;
    WHILE !(parentCId IS NULL) DO
        SET currentCId = parentCId;
        IF `value` THEN
            SELECT fk_parentGroupID FROM `group` WHERE groupId = currentCId INTO parentCId;
        ELSE
            SELECT fk_parentVlaueID FROM valueGroup WHERE valueID = currentCId INTO parentCId;
        END IF;
    END WHILE;
    RETURN currentCId;
END

和

CREATE DEFINER=`root`@`localhost` FUNCTION `getGroupList`(childCId INT) RETURNS VARCHAR(256)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE parentCId INT;
    DECLARE currentCId INT;
    DECLARE labels VARCHAR(256);
    DECLARE label VARCHAR(32);
    SET labels = '';
    SET parentCId = childCId;
    WHILE !(parentCId IS NULL) DO
        SET currentCId = parentCId;
        SELECT fk_parentGroupID, g.label FROM `group` g WHERE groupId = currentCId INTO parentCId, label;
        SET labels = CONCAT(label, ', ', labels);
    END WHILE;
    SET labels = TRIM(TRAILING ',' FROM TRIM(TRAILING ' ' FROM labels));
    RETURN labels;
END

然后你可以简单地执行这样的查询:

SELECT e.equipmentID as equipmentID, e.name as name, getGroupList(vg.fk_groupID) as labels, v.`value` as `value`
FROM `values` v
JOIN valueGroup vg ON vg.valueID = getRootValueGroup(v.fk_valueID, FALSE)
JOIN `group` g ON g.groupID = getRootValueGroup(vg.fk_groupID, TRUE)
JOIN `equipment` e ON e.equipmentID = g.fk_equipmentID

结果看起来与您的有点不同:

|1|widgetA|Dimensions, Overall, External, length|5in
|1|widgetA|Dimensions, Overall, External, length|12.7mm
|1|widgetA|Dimensions, Overall, External, height|1 meter
|1|widgetA|Dimensions, Overall, Internal, length|0.5m
|1|widgetA|Dimensions, Overall, Internal, length|2in
|1|widgetA|Dimensions, Overall, Internal, width |10in
|1|widgetA|Dimensions, Overall, Internal, height|0.25m
|1|widgetA|color                                |blue

但我无法想象你怎么可能得到你在问题中显示的结果。