MySQL存储过程设计问题.是回避还是层次结构? [英] MySQL Stored Procedure Design Problem. Recusion or Hierarchy?

问题描述

假设我们有一个名为SMALLER的表，其中的列num_1和num_2均为整数类型，并且其中包含一些数据.

Suppose we have a table named SMALLER, with column num_1 and num_2, both integer type, and some data in it.

它看起来像这样:

`num_1`  `num_2` 
    1        2
    2        3
    2        8
    3        4
    4        5
        .
        .
        .  Much much much more
        .  

Im试图做的是扩展此表，然后收集所有"Smaller"关系. 这样，结果表应如下所示:

What Im trying to do is expand this table, and then collect all "Smaller" relations. Such that, the result table should looks like this:

`num_1`  `num_2` 
    1        2
    1        3
    1        4
    1        5
    1        8
    2        3
    2        4
    2        5
    2        8
    3        4
    3        5
    4        5

感谢所有帮助！

还有，如果该表不是较小"的关系，而是具有已连接"的关系，例如，"1"连接到"2"，"2"连接到"3"，"2"连接到该怎么办呢? '4'，这样我们说出1-2、1-3、1-4、2-3、2-4.

Futhermore, what if instead of "smaller" relations, this table just have a "connected" relation, for instance, '1' connected to '2', '2' connected to '3', '2' connected to '4', such that we say 1-2, 1-3, 1-4, 2-3, 2-4.

推荐答案

一个好的起点是:

SELECT 
    A.num_1, B.num_2 
FROM 
    Smaller AS A JOIN Smaller AS B ON (A.num_1 < B.num_2) 
ORDER BY A.num_1, B.num_2;

在您的存储过程中，将其放入游标中，在游标上进行迭代，并为每一行执行INSERT IGNORE.即:

Inside your stored procedure, put this into a cursor, iterate over the cursor and for each row do a INSERT IGNORE. Ie:

DECLARE num1,num2 INT;
DECLARE done DEFAULT 0;
DECLARE mycursor CURSOR FOR SELECT # use the select above, im lazy here
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN mycursor;

my_loop: LOOP
    FETCH mycursor INTO num1, num2;
    IF done THEN
       LEAVE my_loop;
    END IF;
    INSERT IGNORE INTO Smaller VALUES (num1,num2);
END LOOP;

要回答您的更新问题，虽然不能完全确定您是通过唯一行之间的关系来表示连接(您需要两列来存储此关系，所以它非常相似).或者，如果您的意思是，您有一个包含所有数字的表，还有另外两个包含第一个表各行之间关系的列表.

To answer your updated question, whiles not entirely sure if you mean connected as by means of relations between unique rows (you would need two columns to store this relation, so it would be quite similar). Or if you mean you have one table containing all numbers, and another two column table containing relations between the rows of the first table.

或者，最后，如果您想要一个仅包含带有"1-2"，"1-3"等字符串的表.如果是这种情况，我将其保留为两个单独的列，并使用CONCAT将它们输出为字符串当您查询表格时:)

Or, finally, if you want a table just containing strings with "1-2", "1-3" etc. If thats the case, I would keep it as two individual columns and just output them as strings using CONCAT when you poll the table :)

这篇关于MySQL存储过程设计问题.是回避还是层次结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！