1、表结构如下:
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 '名称',
PRIMARY KEY (`sf_department_id`) USING BTREE
) COMMENT = '系统管理 部门' ;
2、查询sql语句
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、查询结果
4、函数说明
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