Shulou(Shulou.com)05/31 Report--

This article mainly explains "mysql how to query superior and subordinate organizations", the content of the explanation is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql how to query superior and subordinate organizations" bar!

Train of thought:

Custom mysql method

Using two methods of [FIND_IN_SET] [group_concat] in mysql

(1) prepare the test data table CREATE TABLE `org_ test` (`org_ no` varchar (32) NOT NULL COMMENT 'organization number', `org_ name` varchar 'organization name', `pamphorgno` varchar (32) DEFAULT NULL COMMENT 'parent organization number', PRIMARY KEY (`org_ no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

test data

INSERT INTO `org_ test`VALUES ('1001Qing,' Fujian', null); INSERT INTO `org_ test`VALUES ('100101Qing,' Xiamen', '1001'); INSERT INTO `org_ test`VALUES (' 10010101miles, 'Siming District', '100101'); INSERT INTO `org_ test`VALUES (' 10010102miles, 'Lake District', '100101'); INSERT INTO `org_ test`VALUES (' 10010103miles, 'Tongan District', '100101') INSERT INTO `org_ test` VALUES ('100102,' Fuzhou', '1001'); (2) query all subordinate institutions (including themselves) delimiter $CREATE FUNCTION getOrgChild (orgNo varchar (32)) RETURNS varchar (1000) CHARSET utf8BEGIN-define the temporary variable DECLARE tmpOrg varchar (1000) DEFAULT'' -- Loop query. If orgNo is not empty, then Loop WHILE orgNo IS NOT NULL DO-- splices all query results IF tmpOrg =''THEN SET tmpOrg = CONCAT (tmpOrg, orgNo); ELSE SET tmpOrg = CONCAT (tmpOrg,',', orgNo); END IF -- query data SELECT group_concat (org_no) INTO orgNo FROM org_test WHERE FIND_IN_SET (p_org_no, orgNo) > 0; END WHILE;-- return result RETURN tmpOrg;END $$

Test results:

(3) query all superior institutions of the designated institution (including themselves) delimiter $$CREATE FUNCTION getOrgParent (orgNo varchar (32)) RETURNS varchar (1000) CHARSET utf8BEGIN-define the temporary variable DECLARE tmpOrg varchar (1000) DEFAULT'' -- Loop query. If orgNo is not empty, then Loop WHILE orgNo IS NOT NULL DO-- splices all query results IF tmpOrg =''THEN SET tmpOrg = CONCAT (tmpOrg, orgNo); ELSE SET tmpOrg = CONCAT (tmpOrg,',', orgNo); END IF -- query data SELECT group_concat (p_org_no) INTO orgNo FROM org_test WHERE FIND_IN_SET (org_no, orgNo) > 0; END WHILE;-- return result RETURN tmpOrg;END $$

Test results:

Thank you for your reading, the above is the content of "how to query superior and subordinate organizations in mysql". After the study of this article, I believe you have a deeper understanding of how to query superior and subordinate organizations in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.