mysql树形数据解决方案

一、表结构

CREATE TABLE `t_sys_department`  (
  `sf_department_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `sf_parent_id` bigint(20) NOT NULL DEFAULT -1 COMMENT '上级部门ID',
  `sf_department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `sf_delete` int(11) NOT NULL DEFAULT 0 COMMENT '删除标志：0 否 1 是',
  `sf_creator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '建立人',
  `sf_create_time` datetime(0) NOT NULL COMMENT '建立时间',
  `sf_mender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '修改人',
  `sf_modify_time` datetime(0) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`sf_department_id`) USING BTREE 
) ENGINE = InnoDB AUTO_INCREMENT = 20303 CHARACTER SET = utf8 
COLLATE = utf8_general_ci COMMENT = '系统管理  部门' ROW_FORMAT = Dynamic;

添加数据如下：

二、从某一个根开始正序遍历得到集合

CREATE PROCEDURE `P_Sys_GetDepartTrees`(IN fparentid BIGINT)
BEGIN

DECLARE tmpChd VARCHAR(4000); 
DECLARE ids VARCHAR(4000); 
set ids='';
SET tmpChd = cast(fparentid AS CHAR);
drop TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table SELECT * FROM t_sys_department where 1!=1;

WHILE tmpChd IS NOT NULL DO 
 
if tmpChd != cast(fparentid AS CHAR) then
 SET ids= CONCAT(ids,',',tmpChd);
end if;

SELECT GROUP_CONCAT(sf_department_id) INTO tmpChd FROM t_sys_department WHERE FIND_IN_SET(sf_parent_id,tmpChd)>0;

 
END WHILE; 
 
if fparentid !=-2 then
	SET ids= CONCAT(ids,',',cast(fparentid AS CHAR));
end if;

insert into tmp_table
select * from t_sys_department where  FIND_IN_SET (sf_department_id,ids) and sf_delete=0;
 

select a.*, 
b.sf_department_name as sf_department_name 
from tmp_table a left join t_sys_department b on a.sf_parent_id=b.sf_department_id;

drop TEMPORARY TABLE tmp_table;
END

三、获得每行记录在树形中全路径

SELECT @Pn:=sf_department_id, 
        @pathId:=( SELECT GROUP_CONCAT( SUBSTRING_INDEX( 
		@Pn:= (SELECT CONCAT(sf_parent_id,'|',sf_department_id) FROM t_sys_department WHERE sf_department_id=SUBSTRING_INDEX(@Pn,'|',1)),
            '|', -1 ) ORDER BY sf_department_id DESC SEPARATOR ',' )
        FROM t_sys_department WHERE @Pn IS NOT NULL ORDER BY sf_department_id ASC ) AS path_id
  FROM t_sys_department;

3.1、执行结果

3.2、函数解析

赋值:

select @XX:='abc';  

将列转换为行：

SELECT GROUP_CONCAT(DISTINCT sf_department_id
        ORDER BY sf_department_id ASC SEPARATOR ',')
  FROM t_sys_department;

字符串截取：

substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

截取第一个：

SELECT SUBSTRING_INDEX('15,151,152,16', ',' , 1); //结果是15

截取最后一个：

SELECT SUBSTRING_INDEX('15,151,152,16', ',' , -1); -- 结果是16