In SQL Server, it is easy to use CTE expressions to perform infinite-level parent-child relationship queries; in versions that do not support CTE expressions, it can also be easily implemented with the help of function recursion.
In MySQL, the instance of this requirement is a bit complicated. There are no recursive queries in MySQL, no table-valued functions, and the functions do not support recursion, so they are usually implemented by loops, which seems awkward. Today I saw a recursive query implemented with a single statement. I have a unique idea. I'll share it.
Table structure and data
CREATE TABLE table1(id int, name varchar(10), parent_id int);
INSERT table1 VALUES
(1, ‘Home’, 0),
(2, ‘About’, 1),
(3, ‘Contact’, 1),
(4, ‘Legal’, 2),
(5, ‘Privacy’, 4),
(6, ‘Products’, 1),
(7, ‘Support’, 2);
Query all parents with id = 5
SELECT , DATA.* FROM(
SELECT
@id as _id,
( SELECT @id := parent_id
FROM table1
WHERE id = @id
) as _pid,
@l := @l+1 as level
FROM table1,
(SELECT @id := 5, @l := 0 ) b
WHERE @id > 0
) ID, table1 DATA
WHERE ID._id =
ORDER BY level;
According to this parent query method, it is easy to write all children that search for all children. The following query is id=2
SELECT , DATA.* FROM(
SELECT
@ids as _ids,
( SELECT @ids := GROUP_CONCAT(id)
FROM table1
WHERE FIND_IN_SET(parent_id, @ids)
) as cids,
@l := @l+1 as level
FROM table1,
(SELECT @ids :=’1’, @l := 0 ) b
WHERE @ids IS NOT NULL
) id, table1 DATA
WHERE FIND_IN_SET(, ID._ids)
ORDER BY level, id
(This article is also published on the personal WeChat official account ZJCC)