---

我需要一個 sql 或 hql 查詢來查找特定記錄的所有父母，這應該在 oracle 和 mysql 中工作，例如，如果我有 id：201，父母將是：188、187、1。任何幫助都會不勝感激，謝謝

2011 年 10 月 24 日，我編寫了一些儲存函式來執行祖先和後代遍歷（查找分層欄位的最高級別：有 vs 沒有 CTE）。從我的文章中，您將使用GetAncestry. 從您的數據中，程式碼GetAncestry將遍歷，直到它被-1視為父 ID。parent_id除了更改為之外，不需要更改算法parent。

為了舉例，讓我們假設表被稱為mydb.mytable

獲取祖先程式碼

USE mydb
DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
   DECLARE rv VARCHAR(1024);
   DECLARE cm CHAR(1);
   DECLARE ch INT;

   SET rv = '';
   SET cm = '';
   SET ch = GivenID;
   WHILE ch > 0 DO
       SELECT IFNULL(parent,-1) INTO ch FROM
       (SELECT parent FROM mytable WHERE id = ch) A;
       IF ch > 0 THEN
           SET rv = CONCAT(rv,cm,ch);
           SET cm = ',';
       END IF;
   END WHILE;
   RETURN rv;
END $$
DELIMITER ;

創建此儲存過程後，您將按如下方式呼叫該函式：

USE mydb
SELECT GetAncestry(201);

讓我們測試一下

基於您的問題的範例數據

drop database if exists mydb;
create database mydb;
use mydb
create table mytable
(id int not null,
name varchar(20),
parent int default -1,
primary key (id));
insert into mytable values
(1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
(165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
(169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
(173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
(177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
(181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
(185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
(201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);

載入樣本數據

mysql> drop database if exists mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed
mysql> create table mytable
   -> (id int not null,
   -> name varchar(20),
   -> parent int default -1,
   -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values
   -> (1,'ROOT',-1),(162,'ROOT',1),(163,'ROOT',1),(164,'ROOT',1),
   -> (165,'ROOT',1),(166,'ROOT',1),(167,'ROOT',1),(168,'ROOT',1),
   -> (169,'ROOT',1),(170,'ROOT',1),(171,'ROOT',1),(172,'ROOT',1),
   -> (173,'ROOT',1),(174,'ROOT',1),(175,'ROOT',1),(176,'ROOT',1),
   -> (177,'ROOT',1),(178,'ROOT',1),(179,'ROOT',1),(180,'ROOT',1),
   -> (181,'ROOT',1),(182,'ROOT',1),(183,'ROOT',1),(184,'ROOT',1),
   -> (185,'ROOT',184),(186,'ROOT',1),(187,'ROOT',1),(188,'ROOT',187),
   -> (201,'ROOT',188),(202,'ROOT',201),(203,'ROOT',202);
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql>

創建儲存函式

mysql> USE mydb
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetAncestry` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
   -> DETERMINISTIC
   -> BEGIN
   ->     DECLARE rv VARCHAR(1024);
   ->     DECLARE cm CHAR(1);
   ->     DECLARE ch INT;
   ->
   ->     SET rv = '';
   ->     SET cm = '';
   ->     SET ch = GivenID;
   ->     WHILE ch > 0 DO
   ->         SELECT IFNULL(parent,-1) INTO ch FROM
   ->         (SELECT parent FROM mytable WHERE id = ch) A;
   ->         IF ch > 0 THEN
   ->             SET rv = CONCAT(rv,cm,ch);
   ->             SET cm = ',';
   ->         END IF;
   ->     END WHILE;
   ->     RETURN rv;
   -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

呼叫 GetAncestry

mysql> SELECT GetAncestry(201);
+------------------+
| GetAncestry(201) |
+------------------+
| 188,187,1        |
+------------------+
1 row in set (0.00 sec)

mysql>

只剩下一件事要做…

試一試 ！！！

我已經多次討論在 DBA StackExchange 中使用 GetAncestry

請注意，這是特定於 MySQL 的答案。如果您希望在 PL/SQL 塊或儲存過程中實現算法，請閱讀 PL/SQL 文件。

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