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
标签:COMMENT,排序,name,utf8mb4,house,树形,SET,NULL,结构
From: https://www.cnblogs.com/linhan8888/p/17412101.html