如何为SQLite表定义层级排名的有序结构？

首先明确两种方案的适用场景，没有绝对的最优，得看你的排名结构是线性序列还是层级树形结构：

1. 基于position字段的方案（优化版） #

如果你的排名是单一有序列表（比如青铜→白银→黄金这种线性等级，没有子层级），这个方案更合适，但可以优化原始设计来解决插入痛点：

• 原始问题：插入中间项需要批量更新后续position值
• 优化方法：
  • 改用浮点数作为position类型：比如现有项position是2和3，插入中间项时设为2.5，完全不用改动其他数据；后续再插入可以用2.25、2.75这类值，几乎不会出现间隙用尽的情况。
  • 或者用间隔整数：比如position按10递增（10、20、30...），插入时用15、25这类中间值，等间隙用完后再批量调整一次，减少频繁更新的次数。
• 优点：查询排序简单直接，SELECT * FROM ranks ORDER BY position;就能得到有序结果，性能远高于递归查询。

2. 基于parent_id的递归方案（邻接表） #

如果你的排名是层级树形结构（比如总榜第一下有多个分区第一，每个分区下还有子排名），这个方案是最优选择：

• 建表语句示例：

  CREATE TABLE IF NOT EXISTS ranks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      parent_id INTEGER REFERENCES ranks(id)
  );
  

• 查询整个层级结构用递归CTE：

  WITH RECURSIVE rank_tree AS (
      -- 根节点（顶级排名）
      SELECT id, name, parent_id, 0 AS level
      FROM ranks
      WHERE parent_id IS NULL
      UNION ALL
      -- 递归子节点
      SELECT r.id, r.name, r.parent_id, rt.level + 1
      FROM ranks r
      JOIN rank_tree rt ON r.parent_id = rt.id
  )
  SELECT * FROM rank_tree ORDER BY level, id;
  

• 优点：插入/删除节点时只需要维护当前节点的parent_id，不用修改其他任何数据，适合层级复杂的场景。

总结 #

• 线性无层级的排名：选优化后的position方案，查询简单性能好。
• 层级树形的排名：选parent_id递归方案，维护更灵活。

内容的提问来源于stack exchange，提问作者crazycat256