（MYSQL）根據之前的行選擇行的聯合？

我在 MySQL 中有一個如下所示的表：

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) NOT NULL,
 `data` VARCHAR(255),
 `otherid` int(11),
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

有一些行：

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
3 |  "y"  | NULL
4 |  "z"  | NULL

我想首先選擇 data = “stuff” 的行，然後選擇所有後續行（可能是“無限”），其中下一行由id = lastrow.otherid. 像這樣：

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
4 |  "z"  | NULL

如果沒有帶有 的行data = something，則不應選擇任何行。

有什麼建議？

大約 4.5 年前，我編寫了一個相當激進的儲存過程來遍歷儲存在層次結構中的數據並帶回該表中的所有後代：查找層次結構欄位的最高級別：使用 vs 不使用 CTE

我從 GetFamilyTree 函式中獲取了程式碼，並將其編寫為一個程序給你

儲存過程

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

   DECLARE rv,q,queue,queue_children VARCHAR(1024);
   DECLARE queue_length,front_id,pos INT;

   SET rv = '';

   DROP TABLE IF EXISTS DataFromData;
   CREATE TEMPORARY TABLE DataFromData LIKE mytable;

   SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

   IF @FoundCount = 1 THEN
       SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

       SET rv = @FoundID;
       SET queue = @FoundID;
       SET queue_length = 1;

       WHILE queue_length > 0 DO
           SET front_id = FORMAT(queue,0);
           IF queue_length = 1 THEN
               SET queue = '';
           ELSE
               SET pos = LOCATE(',',queue) + 1;
               SET q = SUBSTR(queue,pos);
               SET queue = q;
           END IF;
           SET queue_length = queue_length - 1;

           SELECT IFNULL(qc,'') INTO queue_children FROM
           (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

           IF LENGTH(queue_children) = 0 THEN
               IF LENGTH(queue) = 0 THEN
                   SET queue_length = 0;
               END IF;
           ELSE
               IF LENGTH(rv) = 0 THEN
                   SET rv = queue_children;
               ELSE
                   SET rv = CONCAT(rv,',',queue_children);
               END IF;
               IF LENGTH(queue) = 0 THEN
                   SET queue = queue_children;
               ELSE
                   SET queue = CONCAT(queue,',',queue_children);
               END IF;
               SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
           END IF;
       END WHILE;

       SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
       PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
   END IF;

   SELECT * FROM DataFromData;

END $$
DELIMITER ;

來自您的問題的樣本數據

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `data` VARCHAR(255),
   `otherid` int(11),
   PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

從您的問題載入的樣本數據

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
   ->     `id` int(11) NOT NULL AUTO_INCREMENT,
   ->     `data` VARCHAR(255),
   ->     `otherid` int(11),
   ->     PRIMARY KEY (id)
   -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

已執行儲存過程

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

警告 #1

由於不知道數據樹的深度，因此使用此方法編寫此方法UNION SELECT並不實用。此程式碼將處理任何深度的數據。

注意事項 #2

此程式碼不處理數據中的循環引用（其中 (id,otherid) 和 (otherid,id) 在此表中共存）。

引用自：https://dba.stackexchange.com/questions/138514