Demand is probably A invited B and C, C invited D, D invited E F G,
the diagram below

Now want to pass A query out all child nodes, including direct and indirect, and now the SQL statements only by A to B C D

Select * from
(
SELECT
Invitation_code
, invited_code
@ le:=the IF (invited_code='0', 0, the IF (LOCATE (CONCAT (' | 'invited_code,' : '), @ pathlevel) & gt; 0, SUBSTRING_INDEX (SUBSTRING_INDEX (@ pathlevel, CONCAT (' | 'invited_code,' : '), 1), '|', 1) + 1, @ le + 1)) levels

, @ pathlevel:=CONCAT (invitation_code @ pathlevel, '|', ':' @ le, '|') pathlevel

@ pathnodes:=the IF (invited_code='0', ', 0 ', CONCAT_WS (', ', the IF (LOCATE (CONCAT (' | 'invited_code,' : '), @ pathall) & gt; 0, SUBSTRING_INDEX (SUBSTRING_INDEX (@ pathall, CONCAT (' | 'invited_code,' : '), 1), '|', 1), @ pathnodes), invited_code) paths

, @ pathall:=CONCAT (invitation_code @ pathall, '|', ':' @ pathnodes, '|') pathall

The FROM geek_user_info,
(SELECT @ le:=0, @ pathlevel:=', @ pathall:=', @ pathnodes:=' ') vv
The ORDER BY invited_code, invitation_code
SRC)
Where the LOCATE (' NEYSVAQN 'paths) & gt; Zero order by invitation_code;

CodePudding user response:

https://blog.csdn.net/nayi_224/article/details/97778698

CodePudding user response:

Mysql 5.7 can use stored procedures to achieve and give you an example of

http://www.msgde.net/mysql/mysql_recursion_select.html

CodePudding user response:

Actually this question is very simple, first of all to confirm two questions,
1, a total of how many nodes,
2, how many layers,
After determine the above two questions, you need to do the following two things,
1, first of all give you all the nodes number (a new database table field ID, this number is stored in a database table within the ID field), the serial number of all nodes in the same length, can use all the Numbers, letters, special symbols,
If you now have 10000 nodes, so the length of number 3, because the keyboard to input characters (letters, Numbers, special symbols) has more than ninety, or ninety carry three digits,
2, the length of each node * biggest layer & lt;=65535, because the mysql varchar type maximum length is 65535,
A new database table field IDS, IDS generated rules are superior IDS + ID at the corresponding level,
After completing the above operations, take a look at your needs
"Demand is A invited B and C, C invited D, D invited E F G"
Assuming that your database tables, seven records, ID is A to G, 7 records,
According to the rules of your invitation, the value of the IDS are as follows:
ID IDS...
A. A
B, type AB
AC C
D the ABCD
E ABCDE
F ABCDF
G FBCDG
The last is a very simple SQL
SELECT * FROM TABLE_NAME ORDER BY IDS.

CodePudding user response:

Have a letter input error, corrected as follows:
ID IDS...
A. A
B, type AB
AC C
D the ABCD
E ABCDE
F ABCDF
G ABCDG