如何用SQL查询向已有数据的表添加Order列且不丢失原有数据

问题描述 #

最近我在做一个项目，需要添加一列对现有记录进行排序。为清晰说明问题，我举一个简单示例：假设我们有一张存储新冠疫苗接种点名称的表，还有一张用于登记各接种点到访人员的表。完成数据收集后，我们发现为了管理排队顺序，需要向Person表添加一个"Order"列。请问如何编写查询语句实现该需求，且不会丢失目前已收集的数据？

以下是接种点表：

以下是人员表：

我期望得到的效果如下：

补充说明：我使用SQL创建表并存储数据。

解决方案 #

你可以按以下步骤实现需求，全程不会影响原有存量数据：

1. 新增排序字段 #

Order是SQL保留关键字，定义字段时需要用对应数据库的标识符包裹避免语法报错：

• MySQL 写法

ALTER TABLE Person ADD COLUMN `Order` INT NULL;

• SQL Server 写法

ALTER TABLE Person ADD [Order] INT NULL;

• PostgreSQL 写法

ALTER TABLE Person ADD "Order" INT NULL;

2. 为存量数据填充各接种点内的排队序号 #

使用窗口函数ROW_NUMBER()按接种点分组，按你需要的规则排序（示例中按人员ID升序，也可以替换为到访登记时间等字段）生成序号，更新到新增字段中：

• 通用写法（支持MySQL8.0+、SQL Server、PostgreSQL等所有支持窗口函数的数据库）

UPDATE Person p
JOIN (
    SELECT 
        PersonID,
        ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY PersonID ASC) AS sort_num
    FROM Person
) t ON p.PersonID = t.PersonID
SET p.`Order` = t.sort_num;

• 低版本MySQL（不支持窗口函数）的兼容写法

SET @site_id = 0, @sort = 0;
UPDATE Person
SET `Order` = IF(SiteID = @site_id, @sort := @sort + 1, @sort := 1 AND @site_id := SiteID)
ORDER BY SiteID, PersonID ASC;

后续新数据插入说明 #

如果需要给后续新登记的人员自动生成对应接种点的排队序号，有两种常用实现方案：

• 插入时主动计算：查询对应接种点的现有最大序号再加1

INSERT INTO Person (Name, SiteID, `Order`)
SELECT '新登记人员姓名', 目标接种点ID, IFNULL(MAX(`Order`), 0) + 1
FROM Person
WHERE SiteID = 目标接种点ID;

• 触发器自动填充：给Person表新增插入触发器，插入数据时自动计算并填充Order字段值。

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