https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query
CREATE TABLE category( category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, parent INT DEFAULT NULL ); INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2), (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7), (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6); SELECT * FROM category ORDER BY category_id; +-------------+----------------------+--------+ | category_id | name | parent | +-------------+----------------------+--------+ | 1 | ELECTRONICS | NULL | | 2 | TELEVISIONS | 1 | | 3 | TUBE | 2 | | 4 | LCD | 2 | | 5 | PLASMA | 2 | | 6 | PORTABLE ELECTRONICS | 1 | | 7 | MP3 PLAYERS | 6 | | 8 | FLASH | 7 | | 9 | CD PLAYERS | 6 | | 10 | 2 WAY RADIOS | 6 | +-------------+----------------------+--------+ |
MySQL 버전 8 이상
To produce the organizational chart with the management chain for each employee (that is, the path from CEO to employee), use a recursive CTE:
WITH RECURSIVE employee_paths (id, name, path) AS ( SELECT id, name, CAST(id AS CHAR(200)) FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) FROM employee_paths AS ep JOIN employees AS e ON ep.id = e.manager_id ) SELECT * FROM employee_paths ORDER BY path; |
The CTE produces this output:
+------+---------+-----------------+ | id | name | path | +------+---------+-----------------+ | 333 | Yasmina | 333 | | 198 | John | 333,198 | | 29 | Pedro | 333,198,29 | | 4610 | Sarah | 333,198,29,4610 | | 72 | Pierre | 333,198,29,72 | | 692 | Tarek | 333,692 | | 123 | Adil | 333,692,123 | +------+---------+-----------------+ |
https://dev.mysql.com/doc/refman/8.0/en/with.html#commn-table-expressions-recursive
WITH RECURSIVE cte_name AS ( initial_query -- anchor member UNION ALL recursive_query -- recursive member that references to the CTE name ) SELECT * FROM cte_name; |
https://www.mysqltutorial.org/mysql-recursive-cte/
Simple MySQL recursive CTE example
WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte_count WHERE n < 3 ) SELECT n FROM cte_count; |
I have a MySQL table which is as follows:
idnameparent_id
19 | category1 | 0 |
20 | category2 | 19 |
21 | category3 | 20 |
22 | category4 | 21 |
... | ... | ... |
Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19] then I should get all its child ids [i.e. result should have ids '20,21,22']....
MySQL 8+
with recursive cte (id, name, parent_id) as ( select id, name, parent_id from products where parent_id = 19 union all select p.id, p.name, p.parent_id from products p inner join cte on p.parent_id = cte.id ) select * from cte; |
MySQL 5.x
For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:
select id, name, parent_id from (select * from products order by parent_id, id) products_sorted, (select @pv := '19') initialisation where find_in_set(parent_id, @pv) and length(@pv := concat(@pv, ',', id)) |
프로시져
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); Recursive Stored procedure: 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 ; Wrapper function for the stored procedure: 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%'; Output: +----+-----------+-----------------------------------------+ | id | name | path | +----+-----------+-----------------------------------------+ | 20 | category2 | category1/category2 | | 21 | category3 | category1/category2/category3 | | 22 | category4 | category1/category2/category3/category4 | +----+-----------+-----------------------------------------+ |
'개발자정보' 카테고리의 다른 글
세일즈포스(Salesforce) Administrative Essentials for New Admins Pre-work (0) | 2021.09.21 |
---|---|
Wijmo 소개 (0) | 2021.09.21 |
npm 설치 (Node.js 설치) (0) | 2021.09.21 |
세일즈포스(Salesfroce) SOQL 예제 총 정리 (0) | 2021.09.20 |
Salesforce Object Query Language ( SOQL ) (0) | 2021.09.20 |