正文

在傳統(tǒng)的后臺管理系統(tǒng)里面經(jīng)常會(huì)需要展示多級菜單關(guān)系，今天我們來學(xué)一下如何使用一條SQL語句展示多級菜單。

現(xiàn)在我們有一張corpinfo單位表，里面有一個(gè)belong字段指向上級單位，首先來看一下現(xiàn)在表里有什么數(shù)據(jù)：

SELECT uid,ubelong FROM corpinfo

現(xiàn)在是類似下面這樣的一個(gè)三級菜單，uid為1的是我們的頂級菜單，ubelog為0。

現(xiàn)在我們想實(shí)現(xiàn)傳入一個(gè) uid ，把當(dāng)前 uid 和其下級單位的 uid 都展示出來，當(dāng)然我們可以使用代碼或者網(wǎng)上常見的存儲(chǔ)過程來實(shí)現(xiàn)，但是今天我們用一條SQL語句來實(shí)現(xiàn)該效果：

先來看看我們的SQL語句

SELECT DATA.uid FROM(
      SELECT
      @ids AS _ids,
      (
      SELECT @ids := GROUP_CONCAT(uid)
      FROM corpinfo
      WHERE FIND_IN_SET(ubelong, @ids)
      ) AS cids,
      @l := @l+1 AS level
      FROM corpinfo, (SELECT @ids := (參數(shù)) , @l := 0 ) b
      WHERE @ids IS NOT NULL
      ) ID, corpinfo DATA
WHERE FIND_IN_SET(DATA.uid, ID._ids)
ORDER BY level,uid

看下執(zhí)行結(jié)果：

可以看到傳入 uid 為 1 后，列出了 uid 為 1 的所有下級單位，連第三級的菜單也列出來了。

比較核心的有下面幾個(gè)地方：

GROUP_CONCAT()函數(shù)

前言：在有 group by 的查詢語句中，select指定的字段要么就包含在 group by 語句的后面，作為分組的依據(jù)，要么就包含在聚合函數(shù)中。

假設(shè)我們有一張 user 用戶表，我們想查看名字相同的用戶的最小年齡，可以這樣寫：

SELECT name,age FROM user GROUP BY name

執(zhí)行結(jié)果為：

現(xiàn)在我們想查詢 name 相同的用戶的所有年齡，當(dāng)然我們可以這樣寫：

SELECT name,age FROM user ORDER BY name

執(zhí)行結(jié)果為：

但是這樣同一個(gè)名字出現(xiàn)多次，看上去非常不直觀。有沒有更直觀的方法，既讓每個(gè)名字都只出現(xiàn)一次，又能夠顯示所有的名字相同的人的id呢？——使用 GROUP_CONCAT() 函數(shù)

功能：將 group by 產(chǎn)生的同一個(gè)分組中的值連接起來，返回一個(gè)字符串結(jié)果。

語法：GROUP_CONCAT( [distinct] 要連接的字段 [ORDER BY 排序字段 ASC/DESC ] [separator '分隔符'] )

SELECT name,GROUP_CONCAT(age) FROM user GROUP BY name

執(zhí)行結(jié)果為：

可以看到相同用戶名的年齡都放到一起了，以逗號分割。

FIND_IN_SET函數(shù)

假設(shè)我們有一張 book 書籍表，有書名和作者兩個(gè)字段：

SELECT name,author FROM book

執(zhí)行結(jié)果為：

現(xiàn)在我們想查作者包含 小A 的書籍，我們來試一下：

SELECT name,author FROM book WHERE author IN ('小A')

執(zhí)行結(jié)果為：

實(shí)際上這樣是不行的，這樣只有當(dāng) author 字段的值等于'小A'時(shí)（和IN前面的字符串完全匹配），查詢才有效，否則都得不到結(jié)果。

可能你會(huì)想到用LIKE實(shí)現(xiàn)，我們來試試看：

SELECT name,author FROM book WHERE author LIKE '%小A%';

執(zhí)行結(jié)果為：

可以看到把小AA的書籍也查出來了，所以用LIKE無法實(shí)現(xiàn)該功能。

那么我們?nèi)绾问褂?nbsp;FIND_IN_SET 函數(shù)來實(shí)現(xiàn)呢？

SELECT name,author FROM book WHERE FIND_IN_SET('小A',author);

執(zhí)行結(jié)果為：

語法：FIND_IN_SET(str,strlist)

str ：要查詢的字符串
strlist ：字段名 參數(shù)以”,”分隔 如 (1,2,6,8)
查詢字段(strlist)中包含(str)的結(jié)果，返回結(jié)果為null或記錄

知道了這兩個(gè)函數(shù)后，現(xiàn)在回過頭來看看前面的SQL語句：

運(yùn)行選中的代碼后可以看到列出了上下級的關(guān)系，至于細(xì)節(jié)這里不再展開描述。

既然我們能查出當(dāng)前單位的所有下級單位，那么應(yīng)該也能查詢所有上級單位，來看下SQL：

   SELECT uid FROM(
        SELECT
        @id AS _id,
        ( SELECT @id := ubelong
        FROM corpinfo
        WHERE uid = @id
        ) AS _pid,
        @l := @l+1 as level
        FROM corpinfo,
        (SELECT @id := (參數(shù)), @l := 0 ) b
        WHERE @id > 0
        ) ID, corpinfo DATA
        WHERE ID._id = DATA.uid
   ORDER BY level DESC

還是我們的corpinfo單位表，執(zhí)行結(jié)果為：

可以看到當(dāng)輸入 uid 為 5 時(shí)，列出了當(dāng)前單位及其上級所有單位，SQL和上面的差不多，這里不再細(xì)說。

最后補(bǔ)充一段代碼，既然我們已經(jīng)拿到想要的單位編號了，接下來就是要遞歸構(gòu)建我們的單位樹了，來看下代碼：

/**
 * 遞歸將模塊樹構(gòu)建成JSON數(shù)組
 */
private JSONArray getJsonArray(List<ClCorpinfo> list) {
    Map<Integer, List<ClCorpinfo>> map = new HashMap<>(16);
    List<ClCorpinfo> sonList;
    for (ClCorpinfo clCorpinfo : list) {
        if (map.get(clCorpinfo.getUbelong()) != null) {
            sonList = map.get(clCorpinfo.getUbelong());
        } else {
            sonList = new ArrayList<>();
        }
        sonList.add(clCorpinfo);
        map.put(clCorpinfo.getUbelong(), sonList);
    }
    JSONArray array = new JSONArray();
    if (list.size() > 0) {
        array = getChildrenTree(map, 0, 0);
    }
    return array;
}
/**
 * 遞歸構(gòu)建模塊樹的子類
 */
public JSONArray getChildrenTree(Map<Integer, List<ClCorpinfo>> map, Integer uparentid, Integer level) {
    JSONArray array = new JSONArray();
    for (ClCorpinfo clCorpinfo : map.get(uparentid)) {
        JSONObject obj = new JSONObject();
        obj.put("uid", clCorpinfo.getUid());
        obj.put("ubelong", clCorpinfo.getUbelong());
        obj.put("ucorpname", clCorpinfo.getUcorpname());
        obj.put("uparentname", clCorpinfo.getUparentname());
        if (map.get(clCorpinfo.getUid()) != null) {
            level++;
            obj.put("children", getChildrenTree(map, clCorpinfo.getUid(), level));
        } else {
            obj.put("children", null);
        }
        array.add(obj);
    }
    return array;
}

上面這段戴安只要傳入單位集合，接下來會(huì)遞歸來構(gòu)建我們的單位樹，接下來只要前端渲染上去就完事了。

總結(jié)

其實(shí)網(wǎng)上也有很多其它的解決方案，比如用代碼實(shí)現(xiàn)，也可以用存儲(chǔ)過程實(shí)現(xiàn)，今天我們使用SQL語句來實(shí)現(xiàn)并不一定是最好的辦法，雖然簡單但是比較難懂，我這邊只是給大伙提供一個(gè)可行的方案，如果有什么不對的地方請多多指教。