MySQL database queries multi-level departments and all user information under it

On the multi-level menu bar or the authority system in the hierarchical tree traversal, Oracle has connect by to implement, MySQL does not have such a convenient way, so MySQL traversal data table is often we encounter headache problem, the following through the database function to achieve

1. Build a table

① Organization table

CREATE TABLE ' t_sys_org ' (
' id ' varchar (+) not NULL COMMENT ' primary key ID ',
' Code ' varchar DEFAULT NULL COMMENT ' code ',
' Name ' varchar ($) DEFAULT NULL COMMENT ' Institution name ',
' FULLNAME ' varchar DEFAULT NULL,
' shortname ' varchar DEFAULT NULL COMMENT ' agency abbreviation ',
' orgcode ' varchar DEFAULT NULL COMMENT ' agency code ',
' ParentID ' varchar (+) DEFAULT NULL COMMENT ' Higher Authority ',
' DEPTH ' int (ten) DEFAULT NULL COMMENT ' depth ',
' sort ' varchar DEFAULT NULL COMMENT ' ordering ',
' REMARK ' varchar ($) DEFAULT NULL COMMENT ' remarks ',
' Status ' varchar (4) DEFAULT NULL COMMENT ' state ',
' Orgtype ' varchar (2) DEFAULT NULL COMMENT ' body type ',
' codenum ' varchar DEFAULT NULL COMMENT ' Unit Code certificate number ',
' Leagalperson ' varchar DEFAULT NULL COMMENT ' institutional legal entity ',
' Leagalpersonid ' int (ten) DEFAULT NULL COMMENT ' owner ID ',
' splitleader ' varchar DEFAULT NULL COMMENT ' in charge of leadership ',
' Splitleaderid ' int (ten) DEFAULT NULL COMMENT ' in charge of leader id ',
' Adminlevel ' varchar (+) DEFAULT NULL COMMENT ' agency administrative level ',
' NATURE ' varchar (+) DEFAULT NULL COMMENT ' institutional nature ',
' Worknatureb ' varchar (+) DEFAULT NULL COMMENT ' mechanism working nature (large Class) ',
' Worknaturem ' varchar (+) DEFAULT NULL COMMENT ' Agency working Nature (middle class) ',
' worknatures ' varchar (+) DEFAULT NULL COMMENT ' mechanism working nature (small Class) ',
' Arecode ' varchar (+) DEFAULT NULL COMMENT ' units are subordinate to the administrative divisions ',
' Address ' varchar (+) DEFAULT NULL COMMENT ' unit station and address ',
' Mailcode ' varchar (+) DEFAULT NULL COMMENT ' unit zip ',
) Engine=innodb DEFAULT Charset=utf8 comment= ' institutional Table (t_sys_org) ';

② User Table

CREATE TABLE ' T_sys_user ' (
' ID ' varchar (+) not NULL COMMENT ' primary key ID ',
' Account ' varchar ($) DEFAULT NULL COMMENT ' accounts ',
' USERNAME ' varchar DEFAULT NULL COMMENT ' name ',
' PASSWORD ' varchar DEFAULT NULL COMMENT ' password ',
' idcard ' varchar DEFAULT NULL COMMENT ' ID number ',
' Sex ' varchar (4) DEFAULT NULL COMMENT ' sex ',
' Createtime ' datetime DEFAULT NULL COMMENT ' creation time ',
' Modtime ' datetime DEFAULT NULL COMMENT ' modified time ',
' Modpwdtime ' datetime DEFAULT NULL COMMENT ' Change password time ',
' REMARK ' varchar ($) DEFAULT NULL COMMENT ' remarks ',
' Status ' Int (ten) DEFAULT NULL COMMENT ' state ',
' Adminlevle ' varchar (+) DEFAULT NULL COMMENT ' administrative level ',
' Alarmbell ' varchar (+) DEFAULT NULL COMMENT ' rank ',
' Archivedeptid ' decimal (10,0) DEFAULT NULL COMMENT ' archive Department ID ',
' AUTHORIZED ' varchar (+) DEFAULT NULL COMMENT ' compilation ',
' BIRTHDAY ' datetime DEFAULT NULL COMMENT ' Birth date ',
' Birthplace ' varchar (+) DEFAULT NULL COMMENT ' birthplace ',
' CreateBy ' decimal (10,0) DEFAULT NULL COMMENT ' creator ',
' EDUCATIONALBG ' varchar (+) DEFAULT NULL COMMENT ' Highest education ',
' Modifyby ' decimal (10,0) DEFAULT NULL COMMENT ' modified person ',
' NATION ' varchar (+) DEFAULT NULL COMMENT ' Nation ',
' Policenumber ' varchar (+) DEFAULT NULL COMMENT ' Siren ',
' Political ' varchar (+) DEFAULT NULL COMMENT ' political face ',
' POSITION ' varchar (+) DEFAULT NULL COMMENT ' duty ',
' Positionlevle ' varchar (+) DEFAULT NULL COMMENT ' rank ',
' Sortno ' varchar ($) DEFAULT NULL COMMENT ' sort level ',
' Workdeptid ' varchar (+) DEFAULT NULL COMMENT ' work unit ID ',
' ORGID ' varchar (+) DEFAULT NULL COMMENT ' owned institution ',
' UserStatus ' decimal (10,0) DEFAULT NULL COMMENT ' User state ',
' Cocallstatus ' decimal (10,0) DEFAULT NULL COMMENT ' im-sync state ',
' Compositiondeptid ' decimal (10,0) DEFAULT NULL COMMENT ' compilation Department ',
' recordsmagorg ' varchar (+) DEFAULT NULL COMMENT ' file Management unit ',
' Nickname ' varchar (+) DEFAULT NULL COMMENT ' nickname ',
' Worknumber ' varchar (+) DEFAULT NULL COMMENT ' work certificate number ',
' UserKey ' text COMMENT ' user key value ',
' Partjobno ' varchar (4) DEFAULT NULL,
' Img_path ' varchar ($) 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 ' (' Workdeptid ') USING BTREE
) Engine=innodb DEFAULT charset=utf8 comment= ' user table ';

3. Use function to obtain a multilevel department number (including the current ID) according to the specified ID serial number
BEGIN
DECLARE stemp VARCHAR (4000);
DECLARE stempchd VARCHAR (4000);

SET stemp = ' $ ';
SET stempchd = cast (orgId as Char);


While stempchd are not NULL does
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, according to the department ID to get all sub-departments under the department
The select ID from the t_sys_org where Find_in_set (ID, queryallchildbyorg (' 448457 ')) Order by code;
5. Obtain all of its user information according to the acquired Sub-department
Select Username, workdeptid from T_sys_user where Workdeptid in select ID from t_sys_org where find_in_set (ID, Queryal lchildbyorg (' 448457 ')) Order by code)

MySQL database queries multi-level departments and all user information under it