MySQL 层级表结构相关脚本

MySQL 层级表结构相关脚本

========================1.刷新排序值=====================

刷新同一层级排序值

SELECT
result.id,
result.parent_id,
result.order_index,
result.new_index
FROM
(
SELECT
tar.id,
tar.parent_id,
tar.order_index,
@rownum := @rownum + 1,
IF
( @pid = tar.parent_id, @rank := @rank + 1, @rank := 1 ) AS new_index,
@pid := tar.parent_id
FROM
( SELECT res_id AS id, IFNULL( parent_id, 0 ) AS parent_id, order_index FROM baseinfo_resource_menu WHERE module = 2 ORDER BY parent_id ASC ) tar,
( SELECT @rownum := 0, @pid := NULL, @rank := 0 ) a
) result;

========================2.刷新编码==========================

查询编码错误数据

SELECT
target.dept_id,
target.parent_id,
target.code,
source.newCode
FROM
baseinfo_department AS target
INNER JOIN (
SELECT
child.dept_id,
child.code AS child_code,
child.order_index,
parent.code AS parent_code,
CONCAT( IFNULL( parent.code, ‘’ ), LPAD( child.order_index, 3, ‘0’ ) ) AS newCode
FROM
baseinfo_department AS child
LEFT JOIN baseinfo_department AS parent ON child.parent_id = parent.dept_id
ORDER BY
parent.code,
child.order_index
) AS source
WHERE
target.module = 0
AND target.dept_id = source.dept_id
AND target.code <> source.newCode;

刷新编码(同一层级排序不可以重复)

UPDATE baseinfo_department AS target
INNER JOIN (
SELECT
child.dept_id,
child.code AS child_code,
child.order_index,
parent.code AS parent_code,
CONCAT( IFNULL( parent.code, ‘’ ), LPAD( child.order_index, 3, ‘0’ ) ) AS newCode
FROM
baseinfo_department AS child
LEFT JOIN baseinfo_department AS parent ON child.parent_id = parent.dept_id
ORDER BY
parent.code,
child.order_index
) AS source ON target.dept_id = source.dept_id
SET target.code = source.newCode
WHERE
target.module = 0;
=====================3.刷新全路径名称=======================

查询全路径错误数据

SELECT
Source.ParentDeptName,
Target.*
FROM
baseinfo_department AS Target
INNER JOIN (
SELECT
IFNULL(
(
SELECT
GROUP_CONCAT( name ORDER BY code SEPARATOR ‘/‘ ) AS ParentDeptName
FROM
baseinfo_department
WHERE
code = LEFT ( DeptInfo.code, LENGTH( code ) )
AND LENGTH( code ) > 3
),
DeptInfo.name
) AS ParentDeptName,
DeptInfo.*
FROM
baseinfo_department AS DeptInfo
) AS Source
WHERE
Target.dept_id = Source.dept_id
AND Target.full_name <> Source.ParentDeptName;

全路径部门名称初始化

UPDATE baseinfo_department AS Target
INNER JOIN (
SELECT
IFNULL(
(
SELECT
GROUP_CONCAT( name ORDER BY code SEPARATOR ‘/‘ ) AS ParentDeptName
FROM
baseinfo_department
WHERE
code = LEFT ( DeptInfo.code, LENGTH( code ) )
AND LENGTH( code ) > 3
),
DeptInfo.name
) AS ParentDeptName,
DeptInfo.*
FROM
baseinfo_department AS DeptInfo
) AS Source ON Target.dept_id = Source.dept_id
SET Target.full_name = Source.ParentDeptName;