For the business of the menu, often exist father-son relationship, such as area generally have a secondary to tertiary, generally if you want to one-time get menu hierarchy, get the upper display area, usually write a recursive code, in the business until conditions after reach a certain critical point, just jumped out, actually writing recursive practice is more troublesome, one is the condition of interrupt recursive once wrong, very easy Easy to loop, two is the efficiency problem, security problem, depth can not control, may lead to stack overflow, so can not directly through the database SQL to find out, save the trouble of processing procedures. Here’s a solution

Here’s an example:

The area table contains an area code. The detailed address of the area must be queried. The table structure is as follows

​

​

It would be nice if the upper layer of 110101 Dongcheng District was found at one time. The following is the SQL I wrote

SELECT
    T1.ID ID,T1.area_name AS area1,T2.area_name AS area2,T3.area_name AS area3,T4.area_name AS area4,T5.area_name  AS area5,
    CASE
      WHEN T1.parent_code = 0
      THEN T1.area_name
      WHEN T2.parent_code = 0
      THEN T2.area_name
      WHEN T3.parent_code = 0
      THEN T3.area_name
      WHEN T4.parent_code = 0
      THEN T4.area_name
      WHEN T5.parent_code = 0
      THEN T5.area_name
      END gov_name
  FROM
   AREA AS T1
    LEFT  JOIN AREA T2
      ON T1.parent_code = T2.area_code
      AND T1.parent_code > 0
     LEFT  JOIN AREA T3
      ON T2.parent_code = T3.area_code
      AND T2.parent_code > 0
      LEFT  JOIN AREA T4
      ON T3.parent_code = T4.area_code
      AND T3.parent_code > 0
      LEFT  JOIN AREA T5
      ON T4.parent_code = T5.area_code
      AND T4.parent_code > 0   
  WHERE T1.area_code=110101
Copy the code

The results are as follows:

​

The reason why area5 is used more often is because if there is a four-level relationship, you can concatenate the fields by saying that area5 is empty and AreA4 is not. For example, if area5 is empty, areA4 is not empty

SELECT
area5,area4,area3,area2,area1,
CASE WHEN area5 IS NULL AND  area4 IS NOT NULL
THEN CONCAT(area4,'_',area3,'_',area2,'_',area1)
WHEN area4 IS NULL AND  area3 IS NOT NULL
THEN CONCAT(area3,'_',area2,'_',area1)
WHEN area3 IS NULL AND  area2 IS NOT NULL
THEN CONCAT(area2,'_',area1)
WHEN area2 IS NULL  
THEN CONCAT(area1)
END sourceName
FROM
(SELECT
    T1.ID ID,T1.area_name AS area1,T2.area_name AS area2,T3.area_name AS area3,T4.area_name AS area4,T5.area_name  AS area5,
    CASE
      WHEN T1.parent_code = 0
      THEN T1.area_name
      WHEN T2.parent_code = 0
      THEN T2.area_name
      WHEN T3.parent_code = 0
      THEN T3.area_name
      WHEN T4.parent_code = 0
      THEN T4.area_name
      WHEN T5.parent_code = 0
      THEN T5.area_name
      END gov_name
  FROM
   AREA AS T1
    LEFT  JOIN AREA T2
      ON T1.parent_code = T2.area_code
      AND T1.parent_code > 0
     LEFT  JOIN AREA T3
      ON T2.parent_code = T3.area_code
      AND T2.parent_code > 0
      LEFT  JOIN AREA T4
      ON T3.parent_code = T4.area_code
      AND T3.parent_code > 0
      LEFT  JOIN AREA T5
      ON T4.parent_code = T5.area_code
      AND T4.parent_code > 0   
  WHERE T1.area_code=110101)A
Copy the code

And that’s what happened

​

So we can figure out all the relationships we need at once, without the complicated logic in the code. The format can be ~ based on the service

I hope this article will help you