保持PositionID不变，按用户优先级重排职位分配的技术问询

一、可行性说明 #

完全可以实现。通过MSSQL的查询排序逻辑结合VB.NET的业务代码，既能在保留PositionID固定的前提下，按用户优先级重新分配职位及关联属性；新增PositionID时的自动重排也能通过数据库触发器或代码后置逻辑实现。

二、具体实现步骤 #

1. 确认核心表结构 #

确保你的两张核心表包含以下关键字段（可根据实际业务调整）：

-- 用户表（示例表名：Users）
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    PriorityRank INT NOT NULL, -- 优先级，数字越小优先级越高
    GetRadio1 VARCHAR(50), -- 用户关联的属性（如需同步到职位表）
    GetRadio2 VARCHAR(50),
    IsActive BIT DEFAULT 1 -- 过滤有效用户
);

-- 职位表（示例表名：Position）
CREATE TABLE Position (
    PositionID INT PRIMARY KEY, -- 固定不变的职位ID
    UserID INT, -- 分配的用户ID
    GetRadio1 VARCHAR(50), -- 职位关联的属性
    GetRadio2 VARCHAR(50)
);

2. 手动触发职位重排（按需执行） #

数据库层面：生成重排映射并更新

通过CTE（公共表表达式）将排序后的用户与按PositionID排序的职位匹配，批量更新职位表：

WITH RankedUsers AS (
    -- 按优先级排序有效用户，生成序号
    SELECT UserID, GetRadio1, GetRadio2,
           ROW_NUMBER() OVER (ORDER BY PriorityRank ASC) AS UserRank
    FROM Users
    WHERE IsActive = 1
),
RankedPositions AS (
    -- 按PositionID排序职位，生成序号
    SELECT PositionID,
           ROW_NUMBER() OVER (ORDER BY PositionID ASC) AS PositionRank
    FROM Position
)
-- 批量更新职位表的用户分配及属性
UPDATE p
SET p.UserID = ru.UserID,
    p.GetRadio1 = ru.GetRadio1, -- 如果GetRadio是用户属性则同步，否则删除此行
    p.GetRadio2 = ru.GetRadio2  -- 同上，若为职位固定属性则移除
FROM Position p
JOIN RankedPositions rp ON p.PositionID = rp.PositionID
LEFT JOIN RankedUsers ru ON rp.PositionRank = ru.UserRank;

VB.NET代码调用

封装成方法，在工具的UI按钮或菜单触发时执行：

Private Sub ReassignPositionsByPriority()
    ' 替换为你的MSSQL连接字符串
    Dim connStr As String = "Server=你的服务器;Database=你的数据库;Integrated Security=True;"
    Dim updateSql As String = "上面的SQL脚本内容"

    Try
        Using conn As New SqlConnection(connStr)
            conn.Open()
            Using cmd As New SqlCommand(updateSql, conn)
                Dim affectedRows As Integer = cmd.ExecuteNonQuery()
                MessageBox.Show($"已完成重排，更新了 {affectedRows} 条职位记录")
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show($"重排失败：{ex.Message}")
    End Try
End Sub

3. 新增PositionID时自动重排 #

提供两种实现方案，按需选择：

方案一：数据库触发器（纯数据库层面实现）

创建AFTER INSERT触发器，当新职位插入后自动触发重排：

CREATE TRIGGER trg_AfterInsertPosition
ON Position
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 阻止返回影响行数的提示
    -- 复用重排的CTE逻辑
    WITH RankedUsers AS (
        SELECT UserID, GetRadio1, GetRadio2,
               ROW_NUMBER() OVER (ORDER BY PriorityRank ASC) AS UserRank
        FROM Users
        WHERE IsActive = 1
    ),
    RankedPositions AS (
        SELECT PositionID,
               ROW_NUMBER() OVER (ORDER BY PositionID ASC) AS PositionRank
        FROM Position
    )
    UPDATE p
    SET p.UserID = ru.UserID,
        p.GetRadio1 = ru.GetRadio1,
        p.GetRadio2 = ru.GetRadio2
    FROM Position p
    JOIN RankedPositions rp ON p.PositionID = rp.PositionID
    LEFT JOIN RankedUsers ru ON rp.PositionRank = ru.UserRank;
END

方案二：VB.NET代码后置逻辑

在新增职位的代码执行完成后，立即调用重排方法：

Private Sub AddNewPosition(newPositionID As Integer)
    Dim connStr As String = "你的MSSQL连接字符串"
    Dim insertSql As String = "INSERT INTO Position (PositionID) VALUES (@PositionID)"

    Try
        Using conn As New SqlConnection(connStr)
            conn.Open()
            ' 执行新增职位操作
            Using cmd As New SqlCommand(insertSql, conn)
                cmd.Parameters.AddWithValue("@PositionID", newPositionID)
                cmd.ExecuteNonQuery()
            End Using
            ' 新增完成后自动触发重排
            ReassignPositionsByPriority()
        End Using
    Catch ex As Exception
        MessageBox.Show($"新增职位失败：{ex.Message}")
    End Try
End Sub

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