机构用户查询-扁平化

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;