1. 复制表

1.1 仅表结构

create table TS_AREA_0709 like TS_AREA where 1 = 2;

1.2 包含表数据

create table TS_AREA_0709 like TS_AREA;

1.3 复制部分表

CREATE TABLE newadmin AS
(  
    SELECT id, username AS uname, password AS pass FROM admin
)

2. 筛选树节点

2.1 树表

WITH RECURSIVE cte AS (
    SELECT id, parent_id
    FROM TS_AREA
    WHERE parent_id = 100000 -- 根节点的条件，可以根据实际情况修改
    UNION ALL
    SELECT a.id, a.parent_id
    FROM TS_AREA a
             INNER JOIN cte c ON c.id = a.parent_id
)
SELECT *
FROM cte;

2.2 非树节点

SELECT *
FROM TS_AREA a
WHERE NOT EXISTS (
    SELECT 1
    FROM area_tree
    WHERE area_tree.id = a.id
);

2.3 删除非树节点

UPDATE TS_AREA
SET DEL_FLAG = 1
WHERE NOT EXISTS (
    SELECT 1
    FROM area_tree
    WHERE area_tree.id = TS_AREA.id
);