On gists

With recursive - CTE

MySql MySql tricks MySql - advanced

cte.sql Raw #

WITH RecursiveSequence AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1
    FROM RecursiveSequence
    WHERE number < 10
)
SELECT * FROM RecursiveSequence;

+--------+
| number |
+--------+
| 1      |
| 2      |
| 3      |
| ...    |
| 10     |
+--------+


/*
 numbers, days interval
*/

WITH RECURSIVE cte(n, dt)  AS  (
	SELECT 10, '2023-01-01'
	UNION ALL
	SELECT n + 10, dt + INTERVAL 1 day 
	FROM cte WHERE n < 1000
)

/*
nebo takto kdyz nechci uvadet za cte(parametry): 

WITH RECURSIVE cte AS (
SELECT 10 AS n, '2023-01-01' AS dt
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)

*/


SELECT * FROM cte;


/* tree, path, level */

# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
 
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL), 
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
 
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name     | role         | manager_id |
+----+----------+--------------+------------+
|  1 | Matthew  | CEO          |       NULL |
|  2 | Caroline | CFO          |          1 |
|  3 | Tom      | CTO          |          1 |
|  4 | Sam      | Treasurer    |          2 |
|  5 | Ann      | Controller   |          2 |
|  6 | Anthony  | Dev Director |          3 |
|  7 | Lousie   | Sys Admin    |          3 |
|  8 | Travis   | Senior DBA   |          3 |
|  9 | John     | Developer    |          6 |
| 10 | Jennifer | Developer    |          6 |
| 11 | Maria    | Junior DBA   |          8 |
+----+----------+--------------+------------+
 


WITH RECURSIVE reporting_chain(id, name, PATH, PATH2,  level) AS 
( 
       SELECT 
		 	id, name, 
			CAST(name AS CHAR(100)), 
			CAST(name AS CHAR(100)), 
			1  
       FROM orgchart 
		 WHERE manager_id IS NULL 
       
		 UNION ALL 
       
		 SELECT 
			oc.id, 
			oc.name, 
			CONCAT(rc.path,' -> ',oc.name), 
			CONCAT(oc.name,' -> ', rc.path2), 
			rc.level+1 
      FROM reporting_chain rc 
		JOIN orgchart oc ON rc.id=oc.manager_id
) 
   
SELECT * FROM reporting_chain ORDER BY level;


+------+----------+---------------------------------------+-------+
| id   | name     | path                                  | level |
+------+----------+---------------------------------------+-------+
|    1 | Matthew  | Matthew                               |     1 |
|    2 | Caroline | Matthew -> Caroline                   |     2 |
|    3 | Tom      | Matthew -> Tom                        |     2 |
|    4 | Sam      | Matthew -> Caroline -> Sam            |     3 |
|    5 | Ann      | Matthew -> Caroline -> Ann            |     3 |
|    6 | Anthony  | Matthew -> Tom -> Anthony             |     3 |
|    7 | Lousie   | Matthew -> Tom -> Lousie              |     3 |
|    8 | Travis   | Matthew -> Tom -> Travis              |     3 |
|    9 | John     | Matthew -> Tom -> Anthony -> John     |     4 |
|   10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer |     4 |
|   11 | Maria    | Matthew -> Tom -> Travis -> Maria     |     4 |
+------+----------+---------------------------------------+-------+




/* node paths */
WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;


/* next tree */
CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);
    
    
    WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;