一种无限级分类的实现

发表于 更新于 分类于

一、概述

插入、更新、删除和移动操作总是保证结构完好；查询使用一条 SELECT 语句即可；查询节点的所有父节点使用 CET 进行递归查询。其中最复杂的部分是更新和移动。

概念 说明
节点 对应一条记录。
父节点 节点的 ParentID 对应的记录。
顶级节点 当 ParentID 为NULL 时，为顶级节点。
节点树 节点及其所有直接和间接子节点形成的树状结构。从数据库角度上看为若干条记录。其中只有顶级父节点的 ParentID 为 NULL ，非顶级父节点的 ParentID 不为 NULL。
层级 Level。顶级节点的 Level 为1，以此类推。
显示序号 DisplayOrder。从1开始计数。

二、表结构

字段名 数据类型 主键 可NULL 备注
GroupID uniqueidentifier √ 分类ID
ParentID uniqueidentifier √ 外键
Name nvarchar(50) 分类名称
Level int 层级。从1开始。
DisplayOrder int 显示顺序。从1开始。

MS SQL Server 表创建脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE [dbo].[Group](
    [ParentID] [uniqueidentifier] NULL,
    [GroupID] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Level] [int] NOT NULL,
    [DisplayOrder] [int] NOT NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
(
    [GroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Group]  WITH CHECK ADD  CONSTRAINT [FK_Group_Group] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Group] ([GroupID])
GO

ALTER TABLE [dbo].[Group] CHECK CONSTRAINT [FK_Group_Group]
GO

为 Level 和 DisplayOrder 字段增加索引。

三、插入( INSERT )

如果作为顶级节点插入( ParentID 为 NULL )，直接将记录添加至最后即可；否则：

1. ParentID

新节点的父节点为 ParentID 对应的节点

2. Level

新节点的 Level 为父节点的 Level + 1

3. DisplayOrder

新节点作为父节点的最后一个节点，其 DisplayOrder 为父节点当前最后一个子节点的 DisplayOrder 增 1。 如果父节点还没有子节点，则 DisplayOrder 为父节点的 DisplayOrder 增 1 ;(换个角度说，新节点的 DisplayOrder，为父节点之后与之同级( Level ) 或层级更小的第一个节点的 DisplayOrder 。如果不存在这样的节点，说明”父节点树”之后没有任何节点了，即为当前所有记录的数量增1。)

4. 在”父节点树”之后的所有节点的 DisplayOrder 增 1

另一种更简单的实现是新节点直接作为父节点的第一个节点，则只需将父节点之后的 DisplayOrder 增 1 即可。

四、删除( DELETE )

  1. 如果预删除节点包含子节点，不允许删除（当然，也可以设计成删除子树，但目前没这样实现）;
  2. 比预删除节点的 DisplayOrder 大的节点的 DisplayOrder 减 1；
  3. 删除节点。
1
2
UPDATE [Group] SET [DisplayOrder] = [DisplayOrder]-1 WHERE [DisplayOrder] > @DisplayOrder;
DELETE [Group] WHERE [GroupID] = @GroupID;

五、移动 (MOVE)

以另一个节点为目标，移到目标节点的上方或下方。如果移动到上方，则作为目标节点的兄弟节点；如果移动到下方，还可选择是作为兄弟节点还是子节点。

如果要移动到目标节点上方，作为另一个节点的子节点呢？那就选择另一个节点作为目标节点吧。

六、更新( UPDATE )

如果仅更新 Name 之类的不影响结构的字段很简单，直接 UPDATE 即可。否则包括这三种情况：

1. 顶 -> 子

从顶级节点移动到某个节点下作为其子节点。
要区分将导致节点上移还是下移，相应的也会影响节点和父节点之间的节点的 DisplayOrder 。

2. 子 -> 顶

从子节点升级为顶级节点。
节点会成为最后一个顶级节点。如果不是想要的顺序，再移动即可。

3. 子 -> 子

作为子节点切换父节点。
同样，要区分将 导致节点上移还是下移，相应的也会影响节点和父节点之间的节点的 DisplayOrder 。

更新节点删除该节点再新增节点——当然不会这样做，因为这样比较低效(也可接受)，并且节点可能和其他表的记录有关联。

七、查询节点路径( CET )

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CET AS
(
  SELECT GroupID, Name, DisplayOrder, ParentID
  FROM [Group]
  WHERE GroupID = @GroupID
  UNION ALL
  SELECT P.GroupID, P.Name, P.DisplayOrder, P.ParentID
  FROM [Group] P
  JOIN CET Curr ON Curr.ParentID = P.GroupID
)
SELECT GroupID, Name, DisplayOrder, ParentID
FROM CET
ORDER BY DisplayOrder

八、查询全部( SELECT )

1
SELECT GroupID, ParentID, [Level], DisplayOrder FROM [Group] ORDER BY DisplayOrder

九、节点树 ( SELECT )

1. 先查询出本节点，主要使用其 Level 和 DisplayOrder

2. 查询出本节点的下一个兄弟节点，假设命名为 DisplayOrderNext

1
SELECT DisplayOrder AS DisplayOrderNext FROM [Group] WHERE DisplayOrder  > @DisplayOrder AND [Level] <= @Level ORDER BY DisplayOrder

3. 如果 DisplayOrderNext 为 NULL，则本节点无兄弟节点且无 Level 更低的节点，一查到底即可。否则见4

1
SELECT GroupID, ParentID, [Level], DisplayOrder FROM [Group] WHERE DisplayOrder >= @DisplayOrder ORDER BY DisplayOrder

4. 如果 DisplayOrderNext 不为 NULL

1
SELECT GroupID, ParentID, [Level], DisplayOrder FROM [Group] WHERE DisplayOrder >= @DisplayOrder AND DisplayOrder < @DisplayOrderNext  AND [Level] >= @Level  ORDER BY DisplayOrder