Uid user ID, mycode my invitation code, pcode my superior invite code, rflag certified,
Table structure as shown in figure, could you tell me how to use the SQL statement or a custom function queries the child nodes of the child node and its descendants,
Uid to 1 user, for example, invited 2 and 3, 2, and 3 continue to invite,
So I statistical u1, query results of 8 (excluding yourself), how to do?

CodePudding user response:

https://dev.mysql.com/doc/refman/8.0/en/with.html

CodePudding user response:

This is the link
First built temporary table - take a few steps associated with several
The create table table_2 as
Select
T.u id,
Count (distinct t1. The uid) u_cnt_1 - level 1
Count (distinct t2. The uid) u_cnt_2 secondary
-The from
(select the uid, mycode p_code from table) t
Left the join
(select the uid, mycode p_code from table) t1
On t.m ycode=t1. P_code
Left the join
(select the uid, mycode p_code from table) t2
On t1. Mycode=t2. P_code
;

Then the number of the need to level a few together

Select
Uid,
U_cnt_1 + u_cnt_2
The from table_2;

CodePudding user response:

The original poster hello, can use a recursive method to do this, the following code

 
The CREATE DEFINER=` root ` @ % ` ` PROCEDURE ` Proc_GetMySub ` (IN ` InUid ` int) 
Label_pro: BEGIN 
DECLARE userCode int the DEFAULT 0; 

The SELECT mycode into userCode FROM tb_xx; 

- to build a temporary table 
DROP TABLE IF the EXISTS tbTem_userId; 
Create table tbTem_userId uid (INT); 

- my first junior 
INSERT INTO tbTem_userId uid FROM the SELECT tb_xx WHERE pcode=userCode; 
- my second junior 
INSERT INTO tbTem_userId uid FROM the SELECT tb_xx WHERE pcode IN (SELECT uid FROM tb_xx WHERE pcode=userCode); 

- inquiries out of all the lower 
SELECT the uid FROM tbTem_userId; 
END 

If you want to query multiple subordinate an analogy, but not more than 5 at a lower level, which USES other recursive query is also very low efficiency, suggest using multistage what, if anything, the original poster design table back according to the application scenarios in order to achieve better query efficiency

CodePudding user response:

In order to realize the function of what you say, with your data structure is possible now, but the efficiency is extremely low,,
Suggest changes, data structure,
With reference to the data of several kinds of tree structure, can easily achieve your requirements, such as materialized paths, value around the tree