Alby's blog

世上没有巧合,只有巧合的假象。

0%

一种无限级分类的实现

一、概述

插入、更新、删除和移动操作总是保证结构完好;查询使用一条 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