树菜单Tree 无限代(SQL递归)

1. 表结构如下：

   
   图片.png

2. 需要展现的结果：

   
   图片.png

DROP TABLE #T2
SELECT  R.id, DE.DEPT_CODE ,
        VA.MEANING AS name ,
        DE.UPPER_DEPT,
        R1.parentId
INTO #T2
FROM    SY_S_DEPARTMENT DE
        INNER JOIN SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE VA ON DE.DEPT_CODE = VA.LOOKUP_VALUE
        INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY DEPT_CODE ASC ) AS id ,
                            DEPT_CODE
                     FROM   SY_S_DEPARTMENT
                   ) AS R ON R.DEPT_CODE = DE.DEPT_CODE
         LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY DEPT_CODE ASC ) AS ParentId ,
                            DEPT_CODE
                     FROM   SY_S_DEPARTMENT
                   ) AS R1 ON R1.DEPT_CODE = DE.UPPER_DEPT
WHERE   VA.LANGUAGE = 'zh-cn';
//SQL 递归查询
WITH    menu ( id, name, DEPT_CODE, UPPER_DEPT, parentId, Level, px, px2 )
          AS ( SELECT   id ,
                        name ,
                        DEPT_CODE ,
                        UPPER_DEPT ,
                        parentId ,
                        0 AS Level ,
                        id px ,
                        CAST(id AS NVARCHAR(4000)) px2
               FROM     #T2
               WHERE    parentId IS NULL
               UNION ALL
               SELECT   A.id ,
                        A.name ,
                        A.DEPT_CODE ,
                        A.UPPER_DEPT ,
                        A.parentId ,
                        B.Level + 1 ,
                        B.px ,
                        B.px2 + LTRIM(A.id)
               FROM     #T2 A
                        INNER JOIN menu B ON A.parentId = B.id
             )
    SELECT  id ,
            name ,
            DEPT_CODE ,
            UPPER_DEPT ,
            CASE WHEN parentId IS NULL AND DEPT_CODE='DO' AND id=1 THEN 0
                 WHEN parentId IS NULL AND DEPT_CODE <>'DO'AND id<>1 THEN 1
                 ELSE parentId
            END AS parentId ,
            Level ,
            px ,
            px2
    FROM    menu
    ORDER BY px ,
            px2 ;

3. 执行结果：

   
   图片.png

4.前台 Tree绑定 界面展示

5.树形菜单所需数据结构

[{"id":1,"parendId":0,"name":"Foods"},
{"id":2,"parentId":1,"name":"Fruits"},
{"id":3,"parentId":1,"name":"Vegetables"},
{"id":4,"parentId":2,"name":"apple"},
{"id":5,"parentId":2,"name":"orange"},
{"id":6,"parentId":3,"name":"tomato"},
{"id":7,"parentId":3,"name":"carrot"},
{"id":8,"parentId":3,"name":"cabbage"},
{"id":9,"parentId":3,"name":"potato"},
{"id":10,"parentId":3,"name":"lettuce"}]

//使用 'loadFilter' 创建树形菜单（Tree）
    $('#tt').tree({
        url: 'data/tree6_data.json',
        loadFilter: function(rows){
            return convert(rows);
        }
    });
//js 组装数据 转换实现  后台传回的dt 转json
 function convert(rows){
        function exists(rows, parentId){
            for(var i=0; i<rows.length; i++){
                if (rows[i].id == parentId) return true;
            }
            return false;
        }
        
        var nodes = [];
        // get the top level nodes
        for(var i=0; i<rows.length; i++){
            var row = rows[i];
            if (!exists(rows, row.parentId)){
                nodes.push({
                    id:row.id,
                    text:row.name
                });
            }
        }
        
        var toDo = [];
        for(var i=0; i<nodes.length; i++){
            toDo.push(nodes[i]);
        }
        while(toDo.length){
            var node = toDo.shift();    // the parent node
            // get the children nodes
            for(var i=0; i<rows.length; i++){
                var row = rows[i];
                if (row.parentId == node.id){
                    var child = {id:row.id,text:row.name};
                    if (node.children){
                        node.children.push(child);
                    } else {
                        node.children = [child];
                    }
                    toDo.push(child);
                }
            }
        }
        return nodes;
    }

    //JS
    //异步刷新
    var quert = function teer ()
    {
        UpperCored = [];
        $.ajax({
            type: "POST",
            url: window.location.protocol + "//" + window.location.host + "@resourceHost/@ViewContext.RouteData.Values["Area"].ToString()/@ViewContext.RouteData.Values["Controller"].ToString()/GetTreedata",
            dataType: "json",
            success: function (data) {
                jondatatree = data.Table;
                jondatacombox = data.Table1;
                $("#cmbCenter").combobox("loadData", jondatacombox);
                funtree(data.Table);
                var url = window.location.protocol + "//" + window.location.host + "@resourceHost/@ViewContext.RouteData.Values["Area"].ToString()/@ViewContext.RouteData.Values["Controller"].ToString()/ComboxDate/" + $('#cmbCenter').combobox('getValue').toString();
                $("#cmbDepartment").combobox('reload', url);
                console.log("loadData");
                //$('#cmbCenter').combobox({onSelect: function (param) { }});
            }
        })
    }

    //树菜单绑定
    function funtree(data) {
        $('#tt').tree({
            animate: true,
            lines: true,
            data: data,
            loadFilter: function (data) {
                return convert(data);
            },
            onClick: function (node) {
                ThisCored(node.id, node.text);
                var index = UpperCored.length - 2;
                $("#cmbCenter").combobox('select', UpperCored[index]);
                $("#cmbDepartment").combobox('select', UpperCored[index - 1]);
                $('#txtBusiness').textbox('setValue', UpperCored.length >= 4 ? node.text : "");
                //$("#tt").tree("loadData", []); 重新加载
            }
        });
    }