你想咋展示？

你这就是一个树的后台存储啊

-- 方案1：邻接表模型

CREATE TABLE TreeAdjacency (

    NodeID INT PRIMARY KEY,

    NodeName NVARCHAR(50) NOT NULL,

    ParentID INT NULL,

    FOREIGN KEY (ParentID) REFERENCES TreeAdjacency(NodeID)

);

-- 插入示例数据

INSERT INTO TreeAdjacency VALUES (1, '根节点', NULL);

INSERT INTO TreeAdjacency VALUES (2, '子节点1', 1);

INSERT INTO TreeAdjacency VALUES (3, '子节点2', 1);

INSERT INTO TreeAdjacency VALUES (4, '子节点1.1', 2);

-- 方案2：路径枚举模型

CREATE TABLE TreePath (

    NodeID INT PRIMARY KEY,

    NodeName NVARCHAR(50) NOT NULL,

    Path NVARCHAR(MAX) NOT NULL -- 存储如'/1/2/4'这样的路径

);

-- 插入示例数据

INSERT INTO TreePath VALUES (1, '根节点', '/1');

INSERT INTO TreePath VALUES (2, '子节点1', '/1/2');

INSERT INTO TreePath VALUES (3, '子节点2', '/1/3');

INSERT INTO TreePath VALUES (4, '子节点1.1', '/1/2/4');

-- 查询所有子节点的递归CTE示例(邻接表)

WITH TreeCTE AS (

    SELECT * FROM TreeAdjacency WHERE NodeID = 1 -- 从根节点开始

    UNION ALL

    SELECT t.* FROM TreeAdjacency t

    JOIN TreeCTE c ON t.ParentID = c.NodeID

)

SELECT * FROM TreeCTE;

with rs1 as(

SELECT * FROM 表名

where typeid = '00000'

),

rs2 as(

SELECT * FROM 表名

where typeid in (

select parid from rs1)

),

rs3 as(

SELECT * FROM 表名

where typeid in (

select parid from rs2)

)

select * from (

select * from rs3

union all

select * from rs2

union all

select * from rs1

)t1

GROUP BY typeid

ORDER BY parid