树形结构在数据库里面查找叶子结点和所有路径

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for b_basic_group
-- ----------------------------
DROP TABLE IF EXISTS `b_basic_group`;
CREATE TABLE `b_basic_group` (
  `id` bigint NOT NULL COMMENT '主键id',
  `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `status` bigint NOT NULL COMMENT '状态',
  `use_org` bigint NOT NULL COMMENT '使用组织',
  `created_org` bigint NOT NULL COMMENT '创建组织',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
  `state` bigint NOT NULL DEFAULT '1' COMMENT '是否可用',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_by` bigint NOT NULL COMMENT '更新人',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `checked_by` bigint DEFAULT NULL COMMENT '审核人',
  `check_time` datetime DEFAULT NULL COMMENT '审核时间',
  `revision` int NOT NULL DEFAULT '0' COMMENT '乐观锁',
  `parent_id` bigint DEFAULT NULL COMMENT '父节点(默认为0,根节点)',
  `specification_num` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规格型号',
  `group_type` bigint DEFAULT NULL COMMENT '分组列表',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT=' ';

-- ----------------------------
-- Records of b_basic_group
-- ----------------------------
BEGIN;
INSERT INTO `b_basic_group` VALUES (1, '1', 140, 1, 1, '泵车', 1, '', 1, '2021-03-17 08:48:51', 1, '2021-03-17 08:48:51', NULL, NULL, 0, 0, NULL, 1);
INSERT INTO `b_basic_group` VALUES (2, '1', 140, 1, 1, '天泵', 1, '', 1, '2021-03-17 09:03:08', 1, '2021-03-17 09:03:08', NULL, NULL, 0, 1, NULL, 1);
INSERT INTO `b_basic_group` VALUES (3, '1', 140, 1, 1, '50米', 1, '', 1, '2021-03-17 09:03:56', 1, '2021-03-17 09:03:56', NULL, NULL, 0, 2, NULL, 1);
INSERT INTO `b_basic_group` VALUES (4, '1', 140, 1, 1, '粉料', 1, '', 1, '2021-03-17 09:30:37', 1, '2021-03-17 09:30:37', NULL, NULL, 0, 0, NULL, NULL);
INSERT INTO `b_basic_group` VALUES (5, '1', 140, 1, 1, '地泵', 1, '', 1, '2021-03-17 09:45:45', 1, '2021-03-17 09:45:45', NULL, NULL, 0, 1, NULL, NULL);
INSERT INTO `b_basic_group` VALUES (6, '1', 140, 1, 1, '60米', 1, '', 1, '2021-03-17 09:46:23', 1, '2021-03-17 09:46:23', NULL, NULL, 0, 5, NULL, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

解决思路:
获取指定树的所有路径

WITH recursive r AS (
	SELECT
		id,
	NAME 
	FROM
		b_basic_group 
	WHERE
		id = 1 UNION ALL
	SELECT
		b_basic_group.id,
		CONCAT( r.NAME, b_basic_group.NAME ) AS NAME 
	FROM
		b_basic_group,
		r 
	WHERE
		r.id = b_basic_group.parent_id 
	) SELECT
	id,
NAME 
FROM
	r 
WHERE
	r.id IN (
	SELECT
		b.id
	FROM
		b_basic_group b 
WHERE
	NOT EXISTS ( SELECT b1.id, b1.parent_id FROM b_basic_group b1, b_basic_group b2 WHERE b1.id = b2.parent_id AND b.id = b1.id ));

关于解释

SELECT
		b.id
	FROM
		b_basic_group b 
WHERE
	NOT EXISTS ( SELECT b1.id, b1.parent_id FROM b_basic_group b1, b_basic_group b2 WHERE b1.id = b2.parent_id AND b.id = b1.id )

not exists里面的
SELECT b1.id, b1.parent_id FROM b_basic_group b1, b_basic_group b2 WHERE b1.id = b2.parent_id 其实是找所有的非叶子结点;
之后后面的筛选b.id = b1.id 是exists的语法风格.

备注:我们可以使用in关键词,下面也是获取所有的叶子结点

SELECT
	b.id,
	b.parent_id 
FROM
	b_basic_group b 
WHERE
	b.id NOT IN (

	SELECT
		b1.id
-- 		b1.parent_id 
	FROM
		b_basic_group b1,
		b_basic_group b2 
	WHERE
		b1.id = b2.parent_id 
-- 	AND b.id = b1.id 
	)