主页 / dba / 问题 / 251778
Accepted
FallenAngel
Asked: 2019-10-24 03:40:46 +0800 CST

根据另一列的值创建序列

  • 772

我必须根据相关用户创建序列号。喜欢：

╔════════════╦═════════════╦═══════════════════╗
║ Row Id     ║ User Id     ║ Sequence Number   ║
╠════════════╬═════════════╬═══════════════════╣
║ 1          ║ 1           ║         1         ║
║ 2          ║ 1           ║         2         ║
║ 3          ║ 2           ║         1         ║
║ 4          ║ 2           ║         2         ║
║ 5          ║ 3           ║         1         ║
║ 6          ║ 1           ║         3         ║
╚════════════╩═════════════╩═══════════════════╝

我有一些先决条件：

  • 每个序列号应该在 1 到 9999 之间。在 9999 之后，它应该跳到 1 并从那里继续。（序列的 CYCLE 属性）
  • 应根据用户 ID 生成序列号。每个 UserId 都应该有自己的序列。
  • 序列号应该是连续的。这样对于用户 ID 5：序列# 123 后面应该跟着 124，不会发生数字的跳跃或重用。

所以使用序列对我来说看起来很合适。但我未能将用户 ID 区别添加到序列中。

CREATE SEQUENCE [dbo].[seq_SequenceNumber] 
AS [smallint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE 
NO CACHE

如何将 UserId 分区添加到此序列？

sql-server sequence

1 个回答

  1. Best Answer

    这将为单个查询生成所需的结果。

    drop table if exists T1;
go

create table T1(RowId int, UserId int, Desired_SequenceNumber int);
go
insert T1
values
(1, 1, 9997),
(2, 1, 9998),
(3, 2, 1),
(4, 2, 2),
(5, 3, 1),
(6, 1, 9999);   -- Have increased User 1's values to test wrap-around.
go

select
    T1.*,
    SequenceNumber = ((ROW_NUMBER() over (partition by UserId order by RowId) - 1) % 9999) + 1
from t1
order by RowId;

    ROW_NUMBER 内置函数为结果中的每一行创建一个顺序整数。partition by UserId确保 UserId 的每个值从 1 重新开始计数。Order by RowId确保 SequenceNumber 的排序与 RowId 的排序匹配。该- 1) % 9999) + 1位确保在 9999 处回绕并重新从 1 开始，而不是从零开始。

    如果必须写入一次序列号并且对于所有后续读取保持一致，则上述操作将不起作用。然后你将需要类似的东西

    ;with NewData as
(
    select *
    from
    (
        values
            (7, 1),
            (8, 4)
    ) as T(RowId, UserId)
),
LatestRow as
(
    select
        UserId,
        RowId = MAX(RowId)
    from T1
    group by UserId
),
NextSequence as
(
    select
        T1.UserId,
        Number = (T1.Desired_SequenceNumber + 1) % 9999
    from T1
    inner join LatestRow
        on LatestRow.RowId = T1.RowId
)
-- insert T1(RowId, UserId, Desired_SequenceNumber)
select
    NewData.RowId,
    NewData.UserId,
    Desired_SequenceNumber = ISNULL(NextSequence.Number, 1)
from NewData
left outer join NextSequence
    on NextSequence.UserId = NewData.UserId;

    为了便于说明，我使用 CTE 对其进行了结构化。它可以重新考虑性能。

    CTE NewData 无需管理表即可引入测试数据。将此替换为您的实际来源。请注意，RowId 7 用于现有用户，8 用于新用户。为了便于参考，我对 RowIds 进行了硬编码。对于此解决方案，这不是必需的。您的表可能使用 IDENTITY，这没关系。

    因为数字环绕解决方案必须找到最近的值，而不是最大值，并从那里递增。我使用 RowId 作为时间代理。CTE LatestRow 为每个现有用户找到最近的，即最大的 / MAX() RowId。如果 RowId 不是单调的，则此解决方案将不起作用，并且需要一些其他时间度量。请注意，间隙是可以的，因此删除和 IDENTITY / SEQUENCE 是可以接受的。根据您的基数，可以通过 INNER 在此 CTE 中加入 NewData 来提高性能。

    CTE NextSequence 将 RowId 映射到现有序列值并计算下一个序列号。模数 (%) 在 9999 处进行环绕。我在您的问题中更改了我的示例数据，因此 UserId 1 将环绕。我假设新数据中每个用户一行。如果还有更多，您可以在新数据中为每个传入行分配一个本地序列，并将 & 模添加到现有的基本序列号。

    最后的选择给出要插入的数据。如果您的真实表有标识，则省略 RowId。使用外连接允许插入新用户。ISNULL 将新用户的序列设置为 1。

    由于您可以命名SEQUENCE对象，因此您可以以一种非常复杂的方式为 UserId 的每个值创建一个新对象，并尝试为每个传入的新行编写从哪个对象中提取的代码。作为一个练习，它会很有趣。作为一个生产系统，这将是令人震惊的。不要这样做。元编码会很糟糕，并且数据库中可用的序列对象的数量会有一些限制。

    • 1

相关问题