MySQL经典实用查询案例，总结整理( 二 ) _MySQL

3、函数查询

查询父级名称

DROP FUNCTION IF EXISTS get_city_parent_name;CREATE FUNCTION `get_city_parent_name`(pid INT) RETURNS varchar(50) CHARSET utf8begindeclare parentName VARCHAR(50) DEFAULT NULL;SELECT city_name FROM ms_city_sort WHERE id=pid into parentName;return parentName;endSELECT t1.*,get_city_parent_name(t1.parent_id) parentName FROM ms_city_sort t1 ;

查询根节点子级

DROP FUNCTION IF EXISTS get_root_child;CREATE FUNCTION `get_root_child`(rootId INT)RETURNS VARCHAR(1000) CHARSET utf8BEGINDECLARE resultIds VARCHAR(500);DECLARE nodeId VARCHAR(500);SET resultIds = '%'; SET nodeId = cast(rootId as CHAR);WHILE nodeId IS NOT NULL DO SET resultIds = concat(resultIds,',',nodeId);SELECT group_concat(id) INTO nodeId FROM ms_city_sort WHERE FIND_IN_SET(parent_id,nodeId)>0;END WHILE;RETURN resultIds; END;SELECT * FROM ms_city_sort WHERE FIND_IN_SET(id,get_root_child(5)) ORDER BY id ;

<END>

【MySQL经典实用查询案例，总结整理】