1. 第一步建表
CREATE TABLE sys_user (
user_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
ns_entity_id varchar(50) DEFAULT NULL COMMENT 'NS实体ID',
dept_id bigint(20) DEFAULT NULL COMMENT '部门ID',
user_name varchar(30) NOT NULL COMMENT '用户账号',
nick_name varchar(30) NOT NULL COMMENT '用户昵称',
email varchar(50) DEFAULT '' COMMENT '用户邮箱',
phonenumber varchar(30) DEFAULT '' COMMENT '手机号码',
sex char(1) NOT NULL DEFAULT '2' COMMENT '用户性别（0男 1女 2未知）',
title varchar(100) DEFAULT NULL COMMENT 'NS岗位',
department varchar(100) DEFAULT NULL COMMENT 'NS部门名称',
supervisor_id bigint(20) DEFAULT NULL COMMENT 'NS主管编号',
supervisor_name varchar(200) DEFAULT NULL COMMENT 'NS主管名称',
subsidiary_id bigint(20) DEFAULT NULL COMMENT 'NS公司编号',
subsidiary_name varchar(200) DEFAULT NULL COMMENT 'NS公司名称',
avatar varchar(100) DEFAULT '' COMMENT '头像地址',
password varchar(100) NOT NULL DEFAULT '' COMMENT '密码',
status char(1) NOT NULL DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
del_flag char(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
login_ip varchar(128) DEFAULT '' COMMENT '最后登录IP',
login_date datetime DEFAULT NULL COMMENT '最后登录时间',
ns_update_time datetime DEFAULT NULL COMMENT 'NS最后修改时间',
create_by varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
create_time datetime NOT NULL COMMENT '创建时间',
update_by varchar(64) DEFAULT '' COMMENT '更新者',
update_time datetime DEFAULT NULL COMMENT '更新时间',
remark varchar(500) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=13914 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
2.插入模拟数据
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (-5, 'Hexin Corp', NULL, 'Hexin Corp', 'Hexin Corp', 'hexincorp@qq.com', '', '2', NULL, NULL, 1, NULL, NULL, NULL, '', '2a10BFaAxhQaOCqHkmE3C452XumvXD65FXJ3Rn5auOL0.bweFVLoi2gv6′,′0′,′0′,′′,NULL,′2021−10−1908:21:00′,′admin′,′2021−11−2316:42:46′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(1,NULL,120,′admin′,′和新′,′admin@hexincorp.cn′,′15888888888′,′1′,NULL,NULL,1,NULL,NULL,NULL,′′,′2a107JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', '2021-09-24 09:02:21', NULL, 'admin', '2021-09-24 09:02:21', '', NULL, '管理员');
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (6674, 'PS61', NULL, '张紫薇', '张紫薇', 'zhangziwei@hexincorp.cn', '', '2', NULL, NULL, 1, NULL, NULL, NULL, '', '2a109J4v8NnqjmLynnEN0C2LwO1waf/F5ThZ6UYr4aNhWbQHjwxMGYqhW′,′0′,′0′,′′,NULL,′2021−11−1108:36:00′,′admin′,′2021−11−2316:42:40′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(6675,′PS62′,NULL,′苏彩琳′,′苏彩琳′,′sucailin@hexincorp.cn′,′′,′2′,NULL,NULL,1,NULL,NULL,NULL,′′,′2a10Y20.MIY1.2CJscBJywXNnecHzle7H9YV77rWI70cNAQ02TE4/9x42', '0', '0', '', NULL, '2021-11-09 09:13:00', 'admin', '2021-11-23 16:42:41', '', NULL, NULL);
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (6678, 'PS63', NULL, '文', '文', '', '', '2', '测试', NULL, 1, NULL, NULL, NULL, '', '2a103ObSTMcz4KElyAK64ZKkk.RzgxRa4r3IO4uvGnYJ9owMiSern1c3a′,′0′,′0′,′′,NULL,′2021−11−1102:14:00′,′admin′,′2021−11−2316:42:41′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(6679,′PS64′,NULL,′文慧敏′,′文慧敏′,′′,′′,′2′,′测试′,NULL,1,NULL,NULL,NULL,′′,′2a102ni.FF4cnqowweQianjcP.F/HsyDCQnzNgGReEiy1SDZPYEOnZAo2', '0', '0', '', NULL, '2021-11-11 02:16:00', 'admin', '2021-11-23 16:42:40', '', NULL, NULL);
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (6681, 'PS65', NULL, '周永辉', '周永辉', '1615393880@qq.com', '', '2', NULL, NULL, 1, NULL, NULL, NULL, '', '2a10ZlB/GkKUWiD/FYGJDybaBu0xxhcVSlXUNWj0Gynlfp35FVDP.CE22′,′0′,′0′,′′,NULL,′2021−11−1209:59:00′,′admin′,′2021−11−2316:42:40′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(13773,′PS66′,NULL,′柯海勇′,′柯海勇′,′kehaiyong@hexincorp.cn′,′′,′2′,NULL,NULL,1,NULL,NULL,NULL,′′,′2a1043BEA/Mk7DWTtt96ntn6ceRHrHq8yQHyCTZFB6bpJrSCTn9G4Li/O', '0', '0', '', NULL, '2021-11-15 09:42:00', 'admin', '2021-11-23 16:42:40', '', NULL, NULL);
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (13847, 'PS67', NULL, '开发员', '开发员', 'zhangyingzhen@hexincorp.cn', '', '2', '开发员', NULL, 1, 'PS56 PS56', NULL, NULL, '', '2a104IrK2gzIXypdLB3XYSQqtOzvC7OedOgt9D1WtT2r2Am4vqAb2LpTq′,′0′,′0′,′′,NULL,′2021−11−1703:25:00′,′admin′,′2021−11−2316:42:40′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(13848,′PS68′,NULL,′张应真′,′张应真′,′zhangyingzhen@hexincorp.cn′,′12345678′,′2′,′开发员′,′技术中心:项目二部（深圳研发中心）′,6139,′PS44张帆′,NULL,NULL,′′,′2a10Pogf76b7Fiq9GVQIJCAm0uO3i9MuXrRUquqpN8pKhcY6Hpp4Lq/O6', '0', '0', '', NULL, '2021-11-23 01:33:00', 'admin', '2021-11-23 16:42:38', '', NULL, NULL);
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (13866, 'PS69', NULL, '屈佳文', '屈佳文', 'qujiawen@hexincorp.cn', '', '2', '后端工程师', NULL, 1, NULL, NULL, NULL, '', '2a102ZPB5nF7AyHL.upu6E07i.Qqrbh3/Sr20ZjD5cpxjmOlmKy1poOHK′,′0′,′0′,′′,NULL,′2021−11−1709:46:00′,′admin′,′2021−11−2316:42:39′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(13867,′PS70′,NULL,′牛志勇′,′牛志勇′,′niuzhiyong@hexincorp.cn′,′′,′2′,NULL,NULL,13866,NULL,NULL,NULL,′′,′2a10zReSJx9EQifwBlGLO2/gbOgCrx/hZg9fo5b3JyeGoS1fahpenlTSG', '0', '0', '', NULL, '2021-11-17 10:11:00', 'admin', '2021-11-23 16:42:39', '', NULL, NULL);
INSERT INTO hexin_cloud.sys_user(user_id, ns_entity_id, dept_id, user_name, nick_name, email, phonenumber, sex, title, department, supervisor_id, supervisor_name, subsidiary_id, subsidiary_name, avatar, password, status, del_flag, login_ip, login_date, ns_update_time, create_by, create_time, update_by, update_time, remark) VALUES (13868, 'PS71', NULL, '易向贵', '易向贵', 'yixianggui@hexincorp.cn', '', '2', NULL, NULL, 13867, NULL, NULL, NULL, '', '2a10WyrWSYc1FIqbjiHSUHDcbOTxTZQpqOisZ/bw2/jVtLfS5CgyYnLiW′,′0′,′0′,′′,NULL,′2021−11−1710:22:00′,′admin′,′2021−11−2316:42:39′,′′,NULL,NULL);INSERTINTO‘hexincloud‘.‘sysuser‘(‘userid‘,‘nsentityid‘,‘deptid‘,‘username‘,‘nickname‘,‘email‘,‘phonenumber‘,‘sex‘,‘title‘,‘department‘,‘supervisorid‘,‘supervisorname‘,‘subsidiaryid‘,‘subsidiaryname‘,‘avatar‘,‘password‘,‘status‘,‘delflag‘,‘loginip‘,‘logindate‘,‘nsupdatetime‘,‘createby‘,‘createtime‘,‘updateby‘,‘updatetime‘,‘remark‘)VALUES(13869,′PS72′,NULL,′朴顺姬′,′朴顺姬′,′piaoshunji@hexincorp.cn′,′′,′2′,NULL,NULL,13868,NULL,NULL,NULL,′′,′2a10LvXDmncSwSiv5od7qsgRFezFowsJLiLST37WTPfrlxAZO54hJGwq2', '0', '0', '', NULL, '2021-11-18 03:04:00', 'admin', '2021-11-23 16:42:39', '', NULL, NULL);
3. 递归查询sql
SELECT
T3.user_id, T3.ns_entity_id, T3.user_name, T3.nick_name
FROM
(
SELECT
@codes AS _ids,
( SELECT @codes := GROUP_CONCAT( user_id ) FROM sys_user WHERE FIND_IN_SET( supervisor_id, @codes ) ) AS T1
FROM
sys_user,
( SELECT @codes := 13866 ) T4
WHERE
@codes IS NOT NULL
) T2,
sys_user T3
WHERE
FIND_IN_SET( T3.user_id, T2._ids )
AND STATUS = 0
AND del_flag = 0
AND user_id != 13866
GROUP BY
T3.user_id;
