MySQL拆分字符串和递归查询

一，最近遇到一个需求，要将数据对市级进行汇总，但是由于系统数据都是很老了的，而且一个字段上存着xxx省/xxx市/xxx区这种数据，但是我们只需要根据市进行相关汇总操作，所以自己写一个函数把原来的字段的值拆分出来只返回xxx市，废话不多说看一下代码吧

//先创建一个临时表
CREATE TABLE `temp_prepare` (
  `area_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '区域名称',
  `source_id` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT '来源id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE DEFINER=`root`@`%` FUNCTION `string_split`(old_string varchar(300),symbol varchar(5),
source_id varchar(32)) RETURNS varchar(100) CHARSET utf8
BEGIN
# 求分割符号','的位置
declare _index int;
declare new_string varchar(100);
 
#清除临时表数据
delete from temp_prepare;
 
set _index = locate(symbol,old_string);
while _index > 0
do
insert into temp_prepare values(left(old_string,_index-1),source_id);#将子字符串存入临时表
set old_string =substr(old_string from _index+1);
set _index = locate(symbol,old_string);
end while;
 
if length(old_string) >= 0 then
insert into temp_prepare values(old_string,source_id);
end if;

select t.area_name into new_string from temp_prepare t where t.source_id = source_id and t.area_name like '%市'
limit 1;

RETURN new_string;
END

然后看一下效果吧：

select h.project_address,string_split(h.project_address,'/',h.id) shi from proj_prepare_header h

二，递归查询当前部门的所有下级，MySQL没有现成的函数方法使用，所以也要自己写一个函数

先看下部门表：

select * from t_post_item;

返回的不包含自身的下级信息 

CREATE DEFINER=`root`@`%` FUNCTION `getChildrenList`(rootId VARCHAR(4000)) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
DECLARE count int;

SET oTemp = '';
SET oTempChild = rootId;

WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM t_post_item WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;

-- 返回的不包含自身的下级信息(先判断有几个逗号)
SET count = LENGTH(oTemp) - LENGTH( REPLACE (oTemp, ',', ''));
-- 逗号超过一个，说明有下级，反之没有下级
IF count > 1 THEN
	SET oTemp = REPLACE(oTemp,CONCAT(',',rootId,','),'');
ELSE
	SET oTemp = REPLACE(oTemp,CONCAT(',',rootId),'');
END IF;

RETURN oTemp;
END

//查询项目经理以及所有下级(不包含自己)
select getChildrenList(id) c_ids from t_post_item where post_code = 'XMJL';

 

返回的包含自身以及下级信息

CREATE DEFINER=`root`@`%` FUNCTION `getChildrenList`(rootId VARCHAR(4000)) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
DECLARE count int;

SET oTemp = '';
SET oTempChild = rootId;

WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM t_post_item WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;

-- 返回的包含自身以及下级信息
SET oTemp = SUBSTRING(oTemp,2);

RETURN oTemp;
END

//查询项目经理以及所有下级(包含自己)
select getChildrenList(id) c_ids from t_post_item where post_code = 'XMJL';