无限级自关联表的排序及下拉框展示

数据结构

部门表 sys_department

通过id和parent_id无限级自关联，唯一根父级为0，同时有一个排序字段order_num，对于同一父级下的所有子部门，通过此字段排序，还有一个“level”字段，维护层级深度

员工表 sys_staff

通过department_id与部门表的id关联

建表语句

DROP TABLE IF EXISTS sys_department;
CREATE TABLE sys_department (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT ‘0’ COMMENT ‘父级部门id’,
name_en varchar(200) DEFAULT ‘’ COMMENT ‘英文名’,
name_cn varchar(200) DEFAULT ‘’ COMMENT ‘中文名’,
level int(11) DEFAULT ‘0’,
order_num int(11) DEFAULT ‘99’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS sys_staff;
CREATE TABLE sys_staff (
id int(11) NOT NULL AUTO_INCREMENT,
department_id int(11) DEFAULT ‘0’ COMMENT ‘部门id’,
name_en varchar(200) DEFAULT ‘’ COMMENT ‘英文名’,
name_cn varchar(200) DEFAULT ‘’ COMMENT ‘中文名’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

功能需求

部门排序

先展示第一个父级部门数据，之后紧跟其下的第一个子部门数据，如果子部门还有子部门继续往下展示，如果没有再展示第二个子部门……

树状图展示：部门+员工

本文使用zTree插件，zTree教程，在此仅讨论数据

下拉框1：部门

下拉框使用插件：select2，附教程
顺序同“部门排序”，有缩进层级效果，所有部门可选

下拉框2：部门+员工

顺序同“部门排序”，没有关联上部门的员工放最前面，所有部门不可选，所有员工可选

功能实现

部门排序

网上自关联的排序很多，但都是按id排的，稍微修改一下，使其按照排序字段order_num排序
先创建一个排序函数

delimiter ~
DROP FUNCTION getPriorityDept~
CREATE FUNCTION getPriorityDept(inID INT) RETURNS VARCHAR(255