In MySQL, there is a set of data, which contains the field primary key: id, parent class ID: pid, id and pid are inherited relationships, and all records associated with its subordinates are queried based on a certain id;
1. Use recursive query syntax. Recursive queries are new features introduced in MySQL 8.0 version, and if your MySQL version is lower than 8.0, you cannot use recursive query syntax.
WITH RECURSIVE sub_items AS (
SELECT id, pid, name
FROM items
WHERE id = 1
UNION ALL
SELECT i.id, i.pid, i.name
FROM items i
INNER JOIN sub_items si ON si.id = i.pid
)
SELECT * FROM sub_items;
In the above statement, Common Table Expressions (CTE) and recursive queries are used to implement it. First, a CTE sub_items is defined, which contains records with ID 1. Then, use UNION ALL to merge this record with its subordinate records and continue recursively until all subordinate records are found. Finally, use the SELECT statement on the outermost layer to query and return these records.
When using it, you need to replace the items in the statement with the actual table name, replace the id, pid, and name with the actual field name, and replace 1 with the ID to be queryed.
2. The following MySQL 8.0 can use self-connection query to implement recursive query:
SELECT * FROM (
SELECT id, pid, name FROM items WHERE id = 1
UNION ALL
SELECT i.id, i.pid, i.name
FROM items i
INNER JOIN (
SELECT * FROM (
SELECT id, pid, name FROM items WHERE id = 1
UNION ALL
SELECT id, pid, name FROM items WHERE pid = 1
) t1
) t2 ON i.pid = t2.id
) t3
In the above statement, two subqueries are used, the first subquery obtains a record with ID 1 and the second subquery obtains a record with ID 1 and its subordinate records. Then, use UNION ALL to merge the results of these two subqueries to implement recursive queries.
When using it, you need to replace the items in the statement with the actual table name, replace the id, pid, and name with the actual field name, and replace 1 with the ID to be queryed.