如何創建MySQL分層遞歸查詢我有一個MySQL表，如下所示：id | name | parent_id19 | category1 | 020 | category2 | 1921 | category3 | 2022 | category4 | 21......現在，我想要一個MySQL查詢，我只提供id [例如說'id = 19']然后我應該得到它的所有子id [即結果應該有id'，21,22']。 ...而且，孩子們的等級不知道它可以變化....另外，我已經有了使用for循環的解決方案.....如果可能的話，讓我知道如何使用單個MySQL查詢來實現相同的功能。
3 回答
ibeautiful
TA貢獻1993條經驗 獲得超6個贊
試試這些：
表定義：
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
parent_id INT,
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category (id)
) engine=innodb;
實驗行：
INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);
遞歸存儲過程：
DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET path = catname;
ELSE
CALL getpath(tempparent, temppath);
SET path = CONCAT(temppath, '/', catname);
END IF;
END$$
DELIMITER ;
存儲過程的包裝函數：
DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getpath(cat_id, res);
RETURN res;
END$$
DELIMITER ;
選擇示例：
SELECT id, name, getpath(id) AS path FROM category;
輸出：
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1 |
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA |
| 24 | categoryB | category1/categoryA/categoryB |
| 25 | categoryC | category1/categoryA/categoryC |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+
過濾具有特定路徑的行：
SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
輸出：
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
添加回答
舉報
0/150
提交
取消