The MySql database queries information about a multi-level department and all its users.

Connectby is available in oracle for tree traversal of the upper and lower levels of departments in a multi-level menu bar or permission system. mysql does not have such a convenient way. Therefore, we often encounter headaches when traversing data tables in MySQL, the following uses database functions to implement 1. Create a table ① mechanism Table CREATETABLE 't_sys_org '('id' varchar (64)

For tree traversal of multiple levels of menu bar or lower-level departments in the permission system, connect by is available in oracle, and mysql does not have such a convenient way, therefore, traversing data tables in MySQL is a common headache, the following uses database functions to implement 1. create table ① mechanism table create table 't_ sys_org '('id' varchar (64)

For tree traversal of multiple levels of menu bar or lower-level departments in the permission system, connect by is available in oracle, and mysql does not have such a convenient way, therefore, MySQL traversal of data tables is a common headache. The following uses database functions to implement

1. Create a table

① Organization table

Create table 't_ sys_org '(
'Id' varchar (64) not null comment 'Primary key id ',
'Code' varchar (60) default null comment' Code ',
'Name' varchar (200) default null comment' institution name ',
'Fullname' varchar (100) default null,
'Shortname 'varchar (60) default null comment' organization abbreviation ',
'Orgcode' varchar (60) default null comment' organization Code ',
'Parentid' varchar (64) default null comment 'parent authorization ',
'Describe' int (10) default null comment 'extension ',
'Sort 'varchar (24) default null comment' sort ',
'Remark' varchar (200) default null comment' COMMENT ',
'Status' varchar (4) default null comment' status ',
'Orgtype' varchar (2) default null comment' institution type ',
'Codec' varchar (80) default null comment' unit code certificate number ',
'Leagalperson' varchar (18) default null comment' organization legal representative ',
'Leagalpersonid' int (10) default null comment 'owner id ',
'Splitleader 'varchar (80) default null comment' in charge of leader ',
'Splitleaderid' int (10) default null comment 'Leader id ',
'Adminlevel' varchar (16) default null comment' institution administrative level ',
'Nature 'varchar (16) default null comment 'Organization type ',
'Worknatureb' varchar (100) default null comment' institution work nature (Category )',
'Worknaturem' varchar (16) default null comment' institution working nature (middle class )',
'Worknatures 'varchar (100) default null comment' organization work nature (small class )',
'Arecode' varchar (16) default null comment' unit subordinate to Administrative Region ',
'Address 'varchar (800) default null comment' unit resident and address ',
'Mailcode' varchar (16) default null comment' unit zip code ',
) ENGINE = InnoDB default charset = utf8 COMMENT = 'Organization table (T_SYS_ORG )';

② User table

Create table 't_ sys_user '(
'Id' varchar (64) not null comment 'Primary key id ',
'Account' varchar (200) default null comment 'account ',
'Username' varchar (60) default null comment 'name ',
'Password'varchar (120) default null comment' password ',
'Idcard' varchar (72) default null comment 'id card No ',
'Sex' varchar (4) default null comment 'gender ',
'Createtime' datetime default null comment' creation time ',
'Modtime' datetime default null comment' modification time ',
'Modpwdtime' datetime default null comment' Change Password time ',
'Remark' varchar (500) default null comment' COMMENT ',
'Status' int (10) default null comment' status ',
'Adminlevle' varchar (16) default null comment' administrative level ',
'Armymbel' varchar (16) default null comment' title ',
'Archivedemotid' decimal (10, 0) default null comment 'archive Department id ',
'Authorized' varchar (16) default null comment' ',
'Birthday' datetime default null comment' birthdate ',
'Birthplace' varchar (128) default null comment' ',
'Createby' decimal (10, 0) default null comment' creator ',
'Educationalbg 'varchar (16) default null comment' highest degree ',
'Modifyby 'decimal (10, 0) default null comment' modifier ',
'Nation' varchar (16) default null comment' nationality ',
'Maximum number' varchar (64) default null comment' alert number ',
'Political id' varchar (16) default null comment' POLITICAL outlook ',
'Position' varchar (16) default null comment' position ',
'Positionlevle' varchar (16) default null comment 'rank ',
'Sortno' varchar (200) default null comment' sorting level ',
'Workdemotid' varchar (64) default null comment' Work Department id ',
'Orgid' varchar (64) default null comment' institution ',
'Userstatus' decimal (10, 0) default null comment 'user status ',
'Callstatus' decimal (10, 0) default null comment' instant synchronization status ',
'Compositiondemotid' decimal (10, 0) default null comment' compilation team ',
'Recordmagorg 'varchar (100) default null comment' Archive Management Unit ',
'Nickname' varchar (64) default null comment' NICKNAME ',
'Worknumber' varchar (100) default null comment' work permit no ',
'Userkey' text comment' User Key value ',
'Partjobno' varchar (4) default null,
'Img _ path' varchar (200) default null comment 'photo path ',
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 '('workdemotid') USING BTREE
) ENGINE = InnoDB default charset = utf8 COMMENT = 'user table ';

3. Use the FUNCTION to obtain the multi-level department ID (including the current ID) based on the specified ID serial number)
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. Obtain all sub-departments under the department according to the department ID
Select id from t_sys_org where FIND_IN_SET (id, queryAllChildByOrg ('20140901') order by code;
5. Obtain all user information based on the obtained sub-department.
Select username, WORKDEPTID from t_sys_user where WORKDEPTID in select id from t_sys_org where FIND_IN_SET (id, queryAllChildByOrg ('20140901') order by code)