如何使用存储过程在mysql中创建树视图 [英] how to create tree view in mysql using stored procedure

查看：42 发布时间：2021/8/30 19:57:54 php mysql stored-procedures tree materialized-path-pattern

本文介绍了如何使用存储过程在mysql中创建树视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我很困惑.我不知道如何使用存储过程在 MySQL 中创建树视图.我尝试在 Google 中搜索，但我不明白如何查询.

我有

deptid |部门代码 |部门名称 |parent_deptid1 1 瓦迪尔乌姆 02 101 bagian umum 13 10101 kepala umum 24 102 bagian privasi 15 1010101 子巴健图 36 1010102 bagian umum 3

我想做成这样

deptid |部门代码 |部门名称 |parent_deptid1 1 瓦迪尔乌姆 02 101 -bagian umum 13 10101 --kepala umum 25 1010101 ---子巴健涂 36 1010102 ---bagan umum 34 102 -bagian privasi 1

解决方案

以下查询将创建标记输出:

SELECT group_concat(康卡特(重复(' ', (CHAR_LENGTH(t.dept_code) - 1)/2),'-',t.dept_name)按 t.dept_code 排序分隔符 '\n') AS 标记从表 1 t

sqlfiddle

结果:

- wadir Umum- 巴吉安乌姆- kepala umum- SUB Bagian Tu- 巴吉安乌姆- bagian privasi

将呈现为:

wadir Umum
- 巴吉安乌姆
  - kepala umum
    - Sub bagian Tu
  - 巴吉安乌姆
- bagian privasi

更新

匹配问题更新:

SELECT t.*,CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', '')) 作为缩进，康卡特(REPEAT('-', CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', ''))),t.dept_name) AS indented_name从表 1 t按 t.dept_code 排序

sqlfiddle

更新 2

您设计的好处是您不需要为此类任务使用存储过程.将 dept_code 视为完整树路径，0 作为分隔符.1010102 也可以写成 1/1/1/2.如果设计正确，您可以通过 dept_code 订购.要获得节点深度，您只需计算路径 (dept_code) 中的分隔符 (0).

更新 3

如果要创建递归结构，最好使用像 PHP 这样的过程语言:

将简单查询 (SELECT * FROM depts) 的 SQL 结果存储到数组中，如下所示:

//查询结果:SELECT * FROM depts$depts = 数组(数组(//第 0 行'deptid' =>1、'dept_code' =>'1','部门名称' =>'wadir Umum','parent_deptid' =>0,),数组(//第 1 行'deptid' =>2、'dept_code' =>'101','部门名称' =>'bagian umum','parent_deptid' =>1、),数组(//第 2 行'deptid' =>3、'dept_code' =>'10101','部门名称' =>'kepala umum','parent_deptid' =>2、),数组(//第 3 行'deptid' =>4、'dept_code' =>'102','部门名称' =>'bagian privasi','parent_deptid' =>1、),数组(//第 4 行'deptid' =>5、'dept_code' =>'1010101','部门名称' =>'Sub bagian Tu','parent_deptid' =>3、),数组(//第 5 行'deptid' =>6、'dept_code' =>'1010102','部门名称' =>'bagian umum','parent_deptid' =>3、),);

使用两个 foreach 循环构建递归结构:

$nodes = array();$roots = array();//初始化节点foreach ($depts 作为 $dept) {$dept['childs'] = array();//初始化孩子$nodes[$dept['deptid']] = $dept;}foreach ($depts 作为 $dept) {如果($dept['parent_deptid'] == 0){$roots[] = $dept['deptid'];//添加根} 别的 {$nodes[$dept['parent_deptid']]['childs'][] = $dept['deptid'];//添加到父母 chlids 列表}}

数组 $roots 和 $nodes 看起来像:

$roots = array (0 => 1,);$nodes = 数组(1 =>大批('deptid' =>1、'dept_code' =>'1','部门名称' =>'wadir Umum','parent_deptid' =>0,'孩子' =>大批(0 =>2、1 =>4、) ,) ,2 =>大批('deptid' =>2、'dept_code' =>'101','部门名称' =>'bagian umum','parent_deptid' =>1、'孩子' =>大批(0 =>3、) ,) ,3 =>大批('deptid' =>3、'dept_code' =>'10101','部门名称' =>'kepala umum','parent_deptid' =>2、'孩子' =>大批(0 =>5、1 =>6、) ,) ,4 =>大批('deptid' =>4、'dept_code' =>'102','部门名称' =>'bagian privasi','parent_deptid' =>1、'孩子' =>大批() ，) ,5 =>大批('deptid' =>5、'dept_code' =>'1010101','部门名称' =>'Sub bagian Tu','parent_deptid' =>3、'孩子' =>大批() ，) ,6 =>大批('deptid' =>6、'dept_code' =>'1010102','部门名称' =>'bagian umum','parent_deptid' =>3、'孩子' =>大批() ，) ,)

演示

现在您可以编写一些递归函数来遍历树:

function getSubtreeHTMLList($deptsids, $nodes) {$result = '';foreach ($deptsids 作为 $deptsid) {$result .='';$result .= $nodes[$deptsid]['dept_name'];如果(计数($nodes[$deptsid]['childs'] > 0)){$result .= getSubtreeHTMLList($nodes[$deptsid]['childs'], $nodes);}$result .='
';}$result .= '</ul>';返回 $result;}echo getSubtreeHTMLList($roots, $nodes);

创建的 HTML:

wadir Umumbagian umumkepala umum;SUb bagian Tu
bagian umum
;</li><li>bagian privasi<ul></ul></li></ul></li></ul>

演示

渲染:

wadir Umum
- bagian umum
  - kepala umum
    - SUb bagian Tu
    - bagian umum
- bagian privasi

I am confused. I don't know how to create a tree view in MySQL using stored procedure. I tried searching in Google and I don't understand how to query.

I have

deptid  |  dept_code |  dept_name  | parent_deptid
     1             1    wadir Umum           0
     2           101   bagian umum           1
     3         10101   kepala umum           2
     4           102   bagian privasi        1
     5       1010101   SUb bagian Tu         3
     6       1010102   bagian umum           3

and I want to make it like this

deptid  |  dept_code |  dept_name  |    parent_deptid
     1             1   wadir Umum                0
     2           101   -bagian umum              1
     3         10101   --kepala umum             2
     5       1010101   ---Sub bagian Tu          3 
     6       1010102   ---bagian umum            3
     4           102   -bagian privasi           1

解决方案

The following query will create a markup output:

SELECT group_concat(
  CONCAT(
    REPEAT('    ', (CHAR_LENGTH(t.dept_code) - 1) / 2),
    '- ',
    t.dept_name
  )
  ORDER BY t.dept_code
  SEPARATOR '\n'
) AS markup
FROM Table1 t

sqlfiddle

Result:

- wadir Umum
    - bagian umum
        - kepala umum
            - SUb bagian Tu
        - bagian umum
    - bagian privasi

Will be rendered to:

wadir Umum
- bagian umum
  - kepala umum
    - SUb bagian Tu
  - bagian umum
- bagian privasi

Update

To match the question update:

SELECT t.*,
  CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', '')) AS indent,
  CONCAT(
    REPEAT('-', CHAR_LENGTH(t.dept_code) - CHAR_LENGTH(REPLACE(t.dept_code, '0', ''))),
    t.dept_name
  ) AS indented_name
FROM Table1 t
ORDER BY t.dept_code

sqlfiddle

Update 2

The benefit of your design is that you do not need a stored procedure for such tasks. See the dept_code as the full tree path with 0 as separator. 1010102 could also be written as 1/1/1/2. If designed correctly you can just order by dept_code. To get node depth you just need to count the separator (0) in the path (dept_code).

Update 3

If you want to create a recursive structure, you better do in a procedural language like PHP:

Store SQL result from a simple query (SELECT * FROM depts) into an array, which would look like:

// result from query: SELECT * FROM depts
$depts = array(
    array( // row #0
        'deptid' => 1,
        'dept_code' => '1',
        'dept_name' => 'wadir Umum',
        'parent_deptid' => 0,
    ),
    array( // row #1
        'deptid' => 2,
        'dept_code' => '101',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 1,
    ),
    array( // row #2
        'deptid' => 3,
        'dept_code' => '10101',
        'dept_name' => 'kepala umum',
        'parent_deptid' => 2,
    ),
    array( // row #3
        'deptid' => 4,
        'dept_code' => '102',
        'dept_name' => 'bagian privasi',
        'parent_deptid' => 1,
    ),
    array( // row #4
        'deptid' => 5,
        'dept_code' => '1010101',
        'dept_name' => 'SUb bagian Tu',
        'parent_deptid' => 3,
    ),
    array( // row #5
        'deptid' => 6,
        'dept_code' => '1010102',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 3,
    ),
);

Build a recursive structure with two foreach loops:

$nodes = array();
$roots = array();

// init nodes
foreach ($depts as $dept) {
    $dept['childs'] = array(); // init childs
    $nodes[$dept['deptid']] = $dept;
}

foreach ($depts as $dept) {
    if ($dept['parent_deptid'] == 0) {
        $roots[] = $dept['deptid']; // add root
    } else {
        $nodes[$dept['parent_deptid']]['childs'][] = $dept['deptid']; // add to parents chlids list
    }
}

The arrays $roots and $nodes will look like:

$roots = array (0 => 1,);
$nodes = array(
    1 => array(
        'deptid' => 1,
        'dept_code' => '1',
        'dept_name' => 'wadir Umum',
        'parent_deptid' => 0,
        'childs' => array(
            0 => 2,
            1 => 4,
        ) ,
    ) ,
    2 => array(
        'deptid' => 2,
        'dept_code' => '101',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 1,
        'childs' => array(
            0 => 3,
        ) ,
    ) ,
    3 => array(
        'deptid' => 3,
        'dept_code' => '10101',
        'dept_name' => 'kepala umum',
        'parent_deptid' => 2,
        'childs' => array(
            0 => 5,
            1 => 6,
        ) ,
    ) ,
    4 => array(
        'deptid' => 4,
        'dept_code' => '102',
        'dept_name' => 'bagian privasi',
        'parent_deptid' => 1,
        'childs' => array() ,
    ) ,
    5 => array(
        'deptid' => 5,
        'dept_code' => '1010101',
        'dept_name' => 'SUb bagian Tu',
        'parent_deptid' => 3,
        'childs' => array() ,
    ) ,
    6 => array(
        'deptid' => 6,
        'dept_code' => '1010102',
        'dept_name' => 'bagian umum',
        'parent_deptid' => 3,
        'childs' => array() ,
    ) ,
)

Demo

Now you can write some recursive function to walk through the tree:

function getSubtreeHTMLList($deptsids, $nodes) {
    $result = '<ul>';
    foreach ($deptsids as $deptsid) {
        $result .= '<li>';
        $result .= $nodes[$deptsid]['dept_name'];
        if (count($nodes[$deptsid]['childs'] > 0)) {
            $result .= getSubtreeHTMLList($nodes[$deptsid]['childs'], $nodes);
        }
        $result .= '</li>';
    }
    $result .= '</ul>';
    return $result;
}

echo getSubtreeHTMLList($roots, $nodes);

Created HTML:

<ul><li>wadir Umum<ul><li>bagian umum<ul><li>kepala umum<ul><li>SUb bagian Tu<ul></ul></li><li>bagian umum<ul></ul></li></ul></li></ul></li><li>bagian privasi<ul></ul></li></ul></li></ul>

Demo

Rendered:

wadir Umum
- bagian umum
  - kepala umum
    - SUb bagian Tu
    - bagian umum
- bagian privasi

这篇关于如何使用存储过程在mysql中创建树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

如何使用存储过程在mysql中创建树视图 [英] how to create tree view in mysql using stored procedure

问题描述

相关文章

PHP最新文章

热门教程

热门工具

登录关闭

如何使用存储过程在mysql中创建树视图 [英] how to create tree view in mysql using stored procedure

问题描述

相关文章

PHP最新文章

热门教程

热门工具

登录 关闭

登录关闭