树形结构排序1

CREATE TABLE `house_structure`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '房源结构id',
  `house_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '房源结构名称',
  `pid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '父级节点id',
  `path` varchar(8000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '完整路径',
  `path_name` varchar(4900) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '完整路径名称',
  `sort` int(0) NULL DEFAULT NULL COMMENT '排序',
  `relevant_files` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '相关附件',
  `remarks` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '建立人ID',
  `create_user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '建立人姓名',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '建立时间',

  PRIMARY KEY (`id`) USING BTREE
) 

            "SELECT " +
            " length( path_name ) - length( REPLACE ( path_name, '>', '' ) ) + 1 AS structureLevel, " +
            " ( " +
            " SELECT " +
            "  GROUP_CONCAT( " +
            "  LPAD( b.sort, 3, 0 )  " +
            " ORDER BY " +
            "  ( length( path_name ) - length( REPLACE ( path_name, '>', '' ) ) + 1 ) ASC SEPARATOR ''  " +
            "  ) AS group_sort  " +
            " FROM " +
            "  house_structure b  " +
            " WHERE " +
            "  FIND_IN_SET( b.id, house_structure.path )  " +
            " ) as groupSort, " +
            "FROM " +
            " house_structure  " +
			

全路径名称替换

SELECT (
             SELECT  
              GROUP_CONCAT(  
              b.house_name   
             ORDER BY  
              ( length( path_name ) - length( REPLACE ( path_name, '>', '' ) ) + 1 ) ASC SEPARATOR '>'   
              ) AS group_sort   
             FROM  
              house_structure b   
             WHERE  
              FIND_IN_SET( b.id, house_structure.path )  ) full_name,
							house_structure.*
						FROM house_structure