mysql 列转行，将列字段按指定字符拆成行数据

实现目标：

• 实现的目标是 将 例如 '0,100,101' 的字段信息，转成 0, 100 ,101 三行
• 通过 SQL 实现，查询 tree 父子数据，如果子节点数据匹配，则将所有上级节点数据也全部查询出来

创建辅助查询表，用以进行自增字典数据储存

DROP TABLE IF EXISTS `incr_num_table`;
CREATE TABLE `incr_num_table` (
 `id` int(19) NOT NULL,
 `num` int(19) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

创建 mysql 存储过程，用以进行自增表初始化数据

DROP PROCEDURE IF EXISTS `incr_num_procedure`;
create procedure incr_num_procedure() 
begin
declare num int; 
set num = 0; 
while num <= 100 do 
insert into incr_num_table(id, num) values(num, num); 
set num = num + 1;
end while;
end;

调用存储过程进行数据初始化

call incr_num_procedure();

sys_dept 表结构与数据

DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept`  (
  `dept_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `parent_id` bigint(20) NULL DEFAULT 0 COMMENT '父部门id',
  `ancestors` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '祖级列表',
  `dept_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '部门名称',
  `order_num` int(4) NULL DEFAULT 0 COMMENT '显示顺序',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 110 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_dept
-- ----------------------------
INSERT INTO `sys_dept` VALUES (100, 0, '0', 'xxx科技', 0);
INSERT INTO `sys_dept` VALUES (101, 100, '0,100', '深圳总公司', 1);
INSERT INTO `sys_dept` VALUES (102, 100, '0,100', '长沙分公司', 2);
INSERT INTO `sys_dept` VALUES (103, 101, '0,100,101', '研发部门', 1);
INSERT INTO `sys_dept` VALUES (104, 101, '0,100,101', '市场部门', 2);
INSERT INTO `sys_dept` VALUES (105, 101, '0,100,101', '测试部门', 3);
INSERT INTO `sys_dept` VALUES (106, 101, '0,100,101', '财务部门', 4);
INSERT INTO `sys_dept` VALUES (107, 101, '0,100,101', '运维部门', 5);
INSERT INTO `sys_dept` VALUES (108, 102, '0,100,102', '市场部门', 1);
INSERT INTO `sys_dept` VALUES (109, 102, '0,100,102', '财务部门', 2);

写 SQL 查询部门名称匹配的数据，如果子部门匹配，则将所有上级部门数据也全部查询出来

select * from sys_dept where dept_id in (
SELECT
    DISTINCT SUBSTRING_INDEX( SUBSTRING_INDEX( dept_id, ',', intab.num + 1), ',', - 1 ) AS dept_id 
FROM
    ( SELECT group_concat( DISTINCT ancestors ) dept_id FROM sys_dept s WHERE s.`dept_name` LIKE '%财务%' ) temp ,
    incr_num_table intab
WHERE
    intab.num <= LENGTH( dept_id ) - LENGTH( REPLACE ( dept_id, ',', '' ) )
    )
UNION 
SELECT * FROM sys_dept s WHERE s.`dept_name` LIKE '%财务%';

查询结果

说明

• 辅助查询表必须从0开始，行数与分隔符的个数有关，行数至少比最多对分隔符个数加1，可以建0~100。
• 另外，也可以使用 MySQL 自带的自增序列表 mysql.help_topic ，但如果用户没有权限操作这些表, 则不能使用。