Optimize the code, I don't know how to write.-CodePudding

Very grateful to the great god,
I have a question, the following code to continue cycle bottom go to, don't know how to write, that I only write at level 5, but the reality may be level 20,,, thank god help me to solve the ah, thank you,

 SELECT id, hhr_name, parent_id tjr_id FROM gbi_hhr WHERE id=90 

OR 
Id IN ((SELECT id FROM gbi_hhr WHERE tjr_id=90 AND sf_type='strategy')) 

OR 
Id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE id IN ((SELECT id FROM gbi_hhr WHERE tjr_id=90 AND sf_type='strategy')))) 

OR 
Id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE id IN ((SELECT id FROM gbi_hhr WHERE tjr_id=90 AND sf_type='strategy'))))) 

OR 
Id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE sf_type!='strategic' AND tjr_id IN (SELECT id FROM gbi_hhr WHERE id IN ((SELECT id FROM gbi_hhr WHERE tjr_id=90 AND sf_type='strategy'))))))

CodePudding user response:

Try using a recursive bai

CodePudding user response:

@ stelf thank you for your answer, how do you write a recursive, can you give me a sample code, thank you, just learn this, don't know much about,

CodePudding user response:

You have serious problem this code design, grade 5 is what concept, you work it out: suppose you have 100 records IN accordance with the 100 x100x100x100x100 time to execute, don't know what you're thinking is what, help you to simplify the second paragraph: id IN ((SELECT id FROM gbi_hhr WHERE tjr_id=90 AND sf_type='strategy')) which can be simplified as: tjr_id=90 AND sf_type='strategic' other of your own,

CodePudding user response:

refer to the second floor weixin_44258890 response:

@ stelf thank you for your answer, how do you write a recursive, can you give me a sample code, thank you, just learn this, don't know much about,

ok, thank you very much,

I was meant to query: recursive query all father-child node (queries all down, and I just want to all the members of the team from the bottom of the query he, and then their performance statistics, then calculate team rewards, they the team may have N layer below, that there are some conditions can't enjoy,)

CodePudding user response:

reference 4 floor weixin_44258890 response:

Quote: refer to the second floor weixin_44258890 response:

@ stelf thank you for your answer, how do you write a recursive, can you give me a sample code, thank you, just learn this, don't know much about,

ok, thank you very much,

I was meant to query: recursive query all father-child node (queries all down, and I just want to all the members of the team from the bottom of the query he, and then their performance statistics, then calculate team rewards, they the team may have N layer below, that there are some conditions, cannot enjoy)

Don't know layer can only can only be done using the function, become a recursive function to go,

CodePudding user response:

Mysql does not support the recursive type of query, can only make functions give you an example:

 DROP FUNCTION IF the EXISTS queryChildrenAreaInfo; 
The CREATE FUNCTION queryChildrenAreaInfo (areaId INT) 
RETURNS a VARCHAR (4000) 
The BEGIN 
DECLARE sTemp VARCHAR (4000); 
DECLARE sTempChd VARCHAR (4000); 

The SET sTemp='$'; 
The SET sTempChd=CAST (areaId AS CHAR); 

WHILE sTempChd IS NOT NULL DO 
The SET sTemp=CONCAT (, sTempChd sTemp, ', '); 
The SELECT GROUP_CONCAT (id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET (parentId, sTempChd) & gt; 0; 
END the WHILE; 
RETURN sTemp. 
END;