How SQL implements the recursive query of MySQL _mssql

As we all know, the current version of MySQL does not support direct recursive query, but through recursion to the iterative transformation of ideas, or in a sentence of SQL to implement the tree recursive query. This benefits from MySQL allowing the @ variable to be used within the SQL statement. The following is the sample code.

Create a table

CREATE TABLE ' treenodes ' (
  ' id ' int,--Node ID
  ' nodename ' varchar (60),--node name
  ' pid ' int--node Parent ID

Inserting test data

INSERT into ' treenodes ' (' id ', ' nodename ', ' pid ') VALUES
(' 1 ', ' A ', ' 0 '), (' 2 ', ' B ', ' 1 '), (' 3 ', ' C ', ' 1 '),
(' 4 ', ' D ') , ' 2 '), (' 5 ', ' E ', ' 2 '), (' 6 ', ' F ', ' 3 '),
(' 7 ', ' G ', ' 6 '), (' 8 ', ' H ', ' 0 '), (' 9 ', ' I ', ' 8 '),
(' Ten ', ' J ', ' 8 '), (' 11 ', ' K ', ' 8 '), (' A ', ' L ', ' 9 '), (' M ', ', '), ' 9 ', (', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ' ', ', ' ', ', ', '
(', ', ' R ', ' 3 '), (',
', ', ' 2 '), (' ', ', ', ' 6 '), (' S ', ' U ', ' 8 ');

Query statement

 Select ID as id,pid as parent ID, levels series between as parent to child, paths as parent to subpath from (
   SELECT id,pid,
   @le: = IF (pid = 0, 0, 
     if ( LOCATE (CONCAT (' | ', PID, ': '), @pathlevel)  > 0,   
         substring_index (Substring_index (@pathlevel, CONCAT (' | '), PID, ': '), -1, ' | ', 1) +1
    , @le + 1) levels
   , @pathlevel: = CONCAT (@pathlevel, ' | ', ID, ': ', @le, ' | ') Pathlevel
   , @pathnodes: = IF (pid =0, ', 0 ', 
      concat_ws (', ',
      IF (LOCATE) (CONCAT (' | ', PID, ': '), @pathall) > 0, 
        Substring_index (Substring_index (@pathall, CONCAT (' | ', PID, ': '), -1), ' | ', 1)
       , @pathnodes), PID) paths
  , @ Pathall:=concat (@pathall, ' | ', ID, ': ', @pathnodes, ' | ') Pathall from 
    TreeNodes, 
  (SELECT @le: =0, @pathlevel: = ", @pathall: = ', @pathnodes: = ') vv order by
  Pid,id
  ) src ORDER by
ID

The final results are as follows:

ID Parent ID Parent to child series parent to Sub Path
------    ------     --------------------    -------------------
1 0 0, 0
2 1 1, 0,1
3 1 1, 0,1
4 2 2, 0,1,2
5 2 2, 0,1,2
6 3 2, 0,1,3
7 6 3, 0,1,3,6
8 0 0, 0
9 8 1, 0,8
10 8 1, 0,8
11 8 1, 0,8
12 9 2, 0,8,9
13 9 2, 0,8,9
14 12 3, 0,8,9,12
15 12 3, 0,8,9,12
16 15 4, 0,8,9,12,15
17 15 4, 0,8,9,12,15
18 3 2, 0,1,3
19 2 2, 0,1,2
20 6 3, 0,1,3,6
21 8 1, 0,8

The above is a SQL to achieve MySQL recursive query implementation of the whole process, I hope that the learning of everyone help.