干掉递归,使用sql一步搞定菜单,区域的查询

       对于业务中的菜单,区域等经常存在父子级关系,一般有二级到三级,一般如果要一次性获取菜单的层级展示,获取区域的上层展示,在业务中代码中通常写递归,直到条件达到某个临界点后,才跳出,其实实际中写递归是比较麻烦的,一是中断递归的条件一旦不对,很容易死循环,二是效率问题,安全问题,深度无法控制,可能会导致栈溢出,所以能不能直接通过数据库sql查出,省去了程序处理的麻烦.下面提供一种解决思路

下面使用个案例讲一下:

有一张区域area表,随便给出个区域码,需要查询出这个区域的详细地址,最多四级关系,表结构如下

如果一次性查出110101东城区的上层结果就好了,下面是我写的sql

 

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

结果如下:

解释下,为啥要多使用area5是因为如果存在四级关系,可以通过area5这个字段为空,area4不为空来进行字段的拼接,比如

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

结果就是这样的

 

就达到了我们一次性查出我们需要的关系,省去了代码中的复杂逻辑.  格式可以根据业务自由拼接~

 

希望这篇文章能帮到大家