机构用户查询-扁平化
sql:
CREATE TABLE `t_call_user` (
`ID` bigint unsigned NOT NULL COMMENT '主键',
`NAME` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`USER_NAME` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
`AGENT_NO` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '坐席号',
`EXT_NUMBER` int DEFAULT NULL COMMENT '分机号',
`VBS_USER` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'vbs账号',
`WORK_FLAG` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工作标志',
`DEL_FLAG` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否删除',
`CREATE_BY` bigint NOT NULL COMMENT '创建人',
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`UPDATE_BY` bigint NOT NULL COMMENT '修改人',
`UPDATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`VER` int DEFAULT NULL COMMENT '版本',
`IS_DIMISSION` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否离职',
PRIMARY KEY (`ID`),
KEY `idx_AGENT_NO_DEL_FLAG` (`AGENT_NO`,`DEL_FLAG`),
KEY `idx_EXT_NUMBER` (`EXT_NUMBER`),
KEY `idx_update_time` (`UPDATE_TIME`),
KEY `idx_USER_NAME` (`USER_NAME`),
KEY `idx_VBS_USER` (`VBS_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户';
CREATE TABLE `t_call_organization` (
`ID` bigint unsigned NOT NULL COMMENT '主键',
`CODE` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '代码',
`NAME` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
`PARENT_ID` bigint DEFAULT NULL COMMENT '父ID',
`CREATE_BY` bigint NOT NULL COMMENT '创建人',
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`UPDATE_BY` bigint NOT NULL COMMENT '修改人',
`UPDATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`DEL_FLAG` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否删除',
`VER` int DEFAULT NULL COMMENT '版本',
PRIMARY KEY (`ID`),
KEY `idx_UPDATE_TIME` (`UPDATE_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='组织架构';
CREATE TABLE `t_call_organization_user` (
`ID` bigint unsigned NOT NULL COMMENT '主键',
`ORG_ID` bigint DEFAULT NULL COMMENT '组织ID',
`USER_ID` bigint DEFAULT NULL COMMENT '用户ID',
`CREATE_BY` bigint NOT NULL COMMENT '创建人',
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`UPDATE_BY` bigint NOT NULL COMMENT '修改人',
`UPDATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`DEL_FLAG` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否删除',
PRIMARY KEY (`ID`),
KEY `idx_ORG_ID` (`ORG_ID`),
KEY `idx_ORG_ID_USER_ID` (`ORG_ID`,`USER_ID`),
KEY `idx_t_call_user_USER_ID` (`USER_ID`),
KEY `idx_update_time` (`UPDATE_TIME`),
KEY `idx_USER_ID_DEL_FLAG` (`USER_ID`,`DEL_FLAG`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='组织用户表';
机构下的用户结构
维信金科
合肥电销
苏州
王馨锐攻坚组
王馨锐常规组
王小二
李小花
成都
组1
张三
李四
组2
希望查询出的结果是
column1 column2 column3 column4
维信金科 苏州 王馨锐攻坚组
维信金科 苏州 王馨锐常规组 王小二
维信金科 苏州 王馨锐常规组 李小花
维信金科 成都 组1 张三
维信金科 成都 组1 李四
WITH RECURSIVE org_hierarchy AS (
SELECT
ID,
NAME,
PARENT_ID,
CAST(NAME AS CHAR(255)) AS path,
1 AS level
FROM
t_call_organization
WHERE
PARENT_ID = 0
and DEL_FLAG = 'N'
UNION ALL
SELECT
o.ID,
o.NAME,
o.PARENT_ID,
CONCAT(p.path, ' > ', o.NAME) AS path,
p.level + 1 AS level
FROM
t_call_organization o
INNER JOIN org_hierarchy p ON o.PARENT_ID = p.ID
where o.DEL_FLAG = 'N'
),
org_user AS (
SELECT
o.path,
o.level,
u.agent_no AS agentNo
FROM
org_hierarchy o
LEFT JOIN t_call_organization_user ou ON o.ID = ou.ORG_ID
LEFT JOIN t_call_user u ON ou.USER_ID = u.ID
where ou.DEL_FLAG='N'
and u.DEL_FLAG='N'
and u.agent_no is not null
)
SELECT
SUBSTRING_INDEX(path, ' > ', 1) AS company,
IF(level > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ' > ', 2), ' > ', -1), NULL) AS workplace,
IF(level > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ' > ', 3), ' > ', -1), NULL) AS group1,
IF(level > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ' > ', 4), ' > ', -1), NULL) AS group2,
agentNo
FROM
org_user
ORDER BY
path, agentNo;