MySql資料庫查詢多級部門及其下的所有使用者資訊

標籤：

             關於多層級功能表列或者許可權系統中部門上下級的樹形遍曆，oracle中有connect by來實現，  mysql沒有這樣的便捷途徑，所以MySQL遍曆資料表是我們經常會遇到的頭痛問題，下面通過資料庫函數來實現

1、建表

     ①  機構表

      CREATE TABLE `t_sys_org` (
    `ID` varchar(64) NOT NULL COMMENT ‘主鍵ID‘,
  `CODE` varchar(60) DEFAULT NULL COMMENT ‘編碼‘,
  `NAME` varchar(200) DEFAULT NULL COMMENT ‘機構名稱‘,
   `FULLNAME` varchar(100) DEFAULT NULL,
   `SHORTNAME` varchar(60) DEFAULT NULL COMMENT ‘機構簡稱‘,
   `ORGCODE` varchar(60) DEFAULT NULL COMMENT ‘機構代碼‘,
`PARENTID` varchar(64) DEFAULT NULL COMMENT ‘上級機構‘,
  `DEPTH` int(10) DEFAULT NULL COMMENT ‘深度‘,
   `SORT` varchar(24) DEFAULT NULL COMMENT ‘排序‘,
         `REMARK` varchar(200) DEFAULT NULL COMMENT ‘備忘‘,
   `STATUS` varchar(4) DEFAULT NULL COMMENT ‘狀態‘,
  `ORGTYPE` varchar(2) DEFAULT NULL COMMENT ‘機構類型‘,
`CODENUM` varchar(80) DEFAULT NULL COMMENT ‘單位代碼證編號‘,
  `LEAGALPERSON` varchar(18) DEFAULT NULL COMMENT ‘機構法人‘,
  `LEAGALPERSONID` int(10) DEFAULT NULL COMMENT ‘負責人ID‘,
  `SPLITLEADER` varchar(80) DEFAULT NULL COMMENT ‘分管領導‘,
  `SPLITLEADERID` int(10) DEFAULT NULL COMMENT ‘分管領導ID‘,
  `ADMINLEVEL` varchar(16) DEFAULT NULL COMMENT ‘機構行政層級‘,
  `NATURE` varchar(16) DEFAULT NULL COMMENT ‘機構性質‘,
  `WORKNATUREB` varchar(100) DEFAULT NULL COMMENT ‘機構工作性質(大類)‘,
  `WORKNATUREM` varchar(16) DEFAULT NULL COMMENT ‘機構工作性質(中類)‘,
  `WORKNATURES` varchar(100) DEFAULT NULL COMMENT ‘機構工作性質(小類)‘,
  `ARECODE` varchar(16) DEFAULT NULL COMMENT ‘單位所隸屬行政區劃‘,
  `ADDRESS` varchar(800) DEFAULT NULL COMMENT ‘單位駐地與地址‘,
        `MAILCODE` varchar(16) DEFAULT NULL COMMENT ‘單位郵編‘,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘機構表（T_SYS_ORG）‘;

    ②使用者表

        CREATE TABLE `t_sys_user` (
  `ID` varchar(64) NOT NULL COMMENT ‘主鍵ID‘,
  `ACCOUNT` varchar(200) DEFAULT NULL COMMENT ‘帳號‘,
   `USERNAME` varchar(60) DEFAULT NULL COMMENT ‘姓名‘,
   `PASSWORD` varchar(120) DEFAULT NULL COMMENT ‘密碼‘,
  `IDCARD` varchar(72) DEFAULT NULL COMMENT ‘社會安全號碼‘,
   `SEX` varchar(4) DEFAULT NULL COMMENT ‘性別‘,
  `CREATETIME` datetime DEFAULT NULL COMMENT ‘建立時間‘,
  `MODTIME` datetime DEFAULT NULL COMMENT ‘修改時間‘,
  `MODPWDTIME` datetime DEFAULT NULL COMMENT ‘修改密碼時間‘,
  `REMARK` varchar(500) DEFAULT NULL COMMENT ‘備忘‘,
  `STATUS` int(10) DEFAULT NULL COMMENT ‘狀態‘,
  `ADMINLEVLE` varchar(16) DEFAULT NULL COMMENT ‘行政層級‘,
   `ALARMBELL` varchar(16) DEFAULT NULL COMMENT ‘警銜‘,
   `ARCHIVEDEPTID` decimal(10,0) DEFAULT NULL COMMENT ‘檔案部門ID‘,
  `AUTHORIZED` varchar(16) DEFAULT NULL COMMENT ‘編製‘,
  `BIRTHDAY` datetime DEFAULT NULL COMMENT ‘出生日期‘,
  `BIRTHPLACE` varchar(128) DEFAULT NULL COMMENT ‘籍貫‘,
 `CREATEBY` decimal(10,0) DEFAULT NULL COMMENT ‘建立人‘,
 `EDUCATIONALBG` varchar(16) DEFAULT NULL COMMENT ‘最高學曆‘,
  `MODIFYBY` decimal(10,0) DEFAULT NULL COMMENT ‘修改人‘,
  `NATION` varchar(16) DEFAULT NULL COMMENT ‘民族‘,
   `POLICENUMBER` varchar(64) DEFAULT NULL COMMENT ‘警號‘,
   `POLITICAL` varchar(16) DEFAULT NULL COMMENT ‘政治面貌‘,
  `POSITION` varchar(16) DEFAULT NULL COMMENT ‘職務‘,
  `POSITIONLEVLE` varchar(16) DEFAULT NULL COMMENT ‘職級‘,
  `SORTNO` varchar(200) DEFAULT NULL COMMENT ‘排序層級‘,
  `WORKDEPTID` varchar(64) DEFAULT NULL COMMENT ‘工作部門ID‘,
  `ORGID` varchar(64) DEFAULT NULL COMMENT ‘所屬機構‘,
 `USERSTATUS` decimal(10,0) DEFAULT NULL COMMENT ‘使用者狀態‘,
   `COCALLSTATUS` decimal(10,0) DEFAULT NULL COMMENT ‘即時通同步狀態‘,
 `COMPOSITIONDEPTID` decimal(10,0) DEFAULT NULL COMMENT ‘編製部門‘,
   `RECORDSMAGORG` varchar(100) DEFAULT NULL COMMENT ‘檔案管理單位‘,
   `NICKNAME` varchar(64) DEFAULT NULL COMMENT ‘暱稱‘,
  `WORKNUMBER` varchar(100) DEFAULT NULL COMMENT ‘工作證號‘,
  `USERKEY` text COMMENT ‘使用者鍵值‘,
   `PARTJOBNO` varchar(4) DEFAULT NULL,
  `IMG_PATH` varchar(200) DEFAULT NULL COMMENT ‘照片路徑‘,
  PRIMARY KEY (`ID`),
   UNIQUE KEY `ACCOUNT_UNIQUE` (`ACCOUNT`) USING BTREE,
   KEY `IND_SUSR_ORGID` (`ORGID`) USING BTREE,
  KEY `IND_SUSR_STAT` (`STATUS`) USING BTREE,
   KEY `IND_SUSR_STNO` (`SORTNO`) USING BTREE,
  KEY `IND_SUSR_USTAT` (`USERSTATUS`) USING BTREE,
  KEY `IND_SUSR_WDPID` (`WORKDEPTID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘使用者表‘;

3、使用FUNCTION根據指定的ID流水號擷取多級部門編號(包括當前ID)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp = ‘$‘;
SET sTempChd = cast(orgId  as char);


WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,‘,‘,sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_sys_org where FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
return sTemp;
END
 
4、根據部門ID擷取該部門下的所有子部門
select id from t_sys_org where  FIND_IN_SET ( id,  queryAllChildByOrg(‘448457‘)) order by code;
5、根據擷取的子部門擷取其所有的使用者資訊
select username, WORKDEPTID from t_sys_user where WORKDEPTID in select id from t_sys_org where  FIND_IN_SET ( id,  queryAllChildByOrg(‘448457‘)) order by code)

MySql資料庫查詢多級部門及其下的所有使用者資訊