mysql - 如何在 MySQL 数据库中将一系列数字链接在一起，两列定义系列连接？

Question

我有一个 MySQL 数据库，它在集合中定义了一系列数字:

set   item1    item2
1     1        2
1     2        3
1     3        4
1     4        5
1     5        6

我想编写一个查询(或多个查询)返回给我这样一个事实:集合 1 是一系列从 1 到 6 的数字。这可能吗？

请注意，我正在处理的真实数据库包含数百个集合，每个集合可以包含一系列可能有点长的项目(我猜每组最多 50 个项目)。另外，我不太确定，但数据库也可能存在数字系列 split 的情况。使用上面的例子，可能有如下实例:

set   item1    item2
1     1        2
1     2        3
1     3        4
1     4        5
1     5        6
1     3        7
1     7        8
1     8        9

在这种情况下，我想知道第 1 组有两个数字系列:[1, 2, 3, 4, 5, 6] 和 [1, 2 , 3, 7, 8, 9]。这是否有可能通过一个查询(或者如果需要，多个查询)实现？

---

编辑:请注意，我按顺序使用数字 1-9 以使问题更容易理解。真实数据更加困惑，没有那么有序。

Answer 1

如您所知，MySQL 无法“开箱即用”地处理递归，因此选项包括:

1. 编写存储过程
2. 从邻接表切换到替代模型(例如嵌套集)
3. 根据需要经常将表连接到自身
4. 处理应用程序级代码中的递归(例如一点 PHP)

这是一个使用选项 3 的示例，但它可以很容易地适应选项 4...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(
    family_id INT NOT NULL,
    item_id INT NOT NULL,
    parent_id INT NULL,

    PRIMARY KEY(family_id, item_id)
);

INSERT INTO my_table 
VALUES (101, 1, null), (101, 2, 1), (101, 3, 2), (101, 4, 3),
       (101, 5, 4), (101, 6, 5), (101, 7, 3), (101, 8, 7), (101, 9, 8);

SELECT CONCAT_WS(','
     , a.item_id 
     , b.item_id
     , c.item_id
     , d.item_id
     , e.item_id
     , f.item_id 
     , g.item_id
     , h.item_id
     , i.item_id
     ) series
FROM 
    my_table a
LEFT JOIN 
    my_table b ON b.parent_id = a.item_id AND b.family_id = a.family_id
LEFT JOIN 
    my_table c ON c.parent_id = b.item_id AND c.family_id = b.family_id
LEFT JOIN 
    my_table d ON d.parent_id = c.item_id AND d.family_id = c.family_id
LEFT JOIN 
    my_table e ON e.parent_id = d.item_id AND e.family_id = d.family_id
LEFT JOIN 
    my_table f ON f.parent_id = e.item_id AND f.family_id = e.family_id
LEFT JOIN 
    my_table g ON g.parent_id = f.item_id AND g.family_id = f.family_id
LEFT JOIN 
    my_table h ON h.parent_id = g.item_id AND h.family_id = g.family_id
LEFT JOIN 
    my_table i ON i.parent_id = h.item_id AND i.family_id = h.family_id
WHERE 
    a.parent_id IS NULL;

  +-------------+
  | series      |
  +-------------+
  | 1,2,3,4,5,6 |
  | 1,2,3,7,8,9 |
  +-------------+