mysql - 产品 alpha-num 序列号的数学运算 |数据库设计

Question

所以在我的数据库中，我得到了表格

Product (prodId,Name,Price)
Box (BoxId,prodId,From,To,Available)

'From'代表第一个序号。和“To”结尾的连续剧。计算“To”子“From”给出产品的数量。

一位客户来了并订购了给定数量的给定产品。我需要的是“发件人”序列号，我计算“发件人”+数量。如果序列号只是连续的整数。这很容易。但这适用于具有不同序列号的所有类型的产品。例如:

Box( 1,1,ABC00000C,ABC00099K,100)

我想要实现的是:

SELECT From + 50 FROM BOX

如何处理序列号以获得订单结束序列号？

Answer 1

要处理此类序列号，您需要(选项 1)fn(x) 计算给定整数 x 的序列号，或(选项 2)可用序列号列表。

方案一最容易实现，但它需要想出序号格式的人确实也想过编一个转换公式把整数转换成序号。如果存在这样的公式，您需要做的就是确定“from”值的整数值，将此整数值“x”加 50 并确定“x + 50”的序列号。

选项 2 要求您有一个列表，或者可以生成一个序列号列表，而且这些序列号必须(以某种方式)按逻辑顺序排列。选项 2 然后应用 SQL Server 提供的多种方法之一从该列表中获取接下来的 50 行，从其中值为“From”的行开始。这种方法的例子是“select top (50) ...”，窗口函数“row_number() over (order by ...)”，“select ... order by ... offset n rows fetch next 50 rows only "甚至是光标。

在 Wildfire 发表评论后添加:

我建议您创建一个表来保存选项 2 的序列号。让我举一个例子来解释这一点:如果一个序列号为 n + 5 的项目碰巧从生产线上掉下来并且损坏得无法修复，您会怎么做？ IE。这个序列号永远不会运送给客户。我敢打赌，当发生这种情况时，您不会运送一箱少一件的元素，也不会因为一件元素丢失而丢弃 49 件未损坏的产品。相反，您可能会将序列号为 n 到 n + 4 和序列号为 n + 6 到 n + 51 的所有产品放在一个盒子里，而将序列号 n + 5 留在外面。在一个完美的世界中，这当然永远不会发生，但在现实生活中，有时确实会出问题，因此您需要能够应对 - 例如 - 丢失连续剧。所以我真的建议创建一个表，其中包含所有可用于装箱的连续剧，然后让您的装箱过程从该表中读取接下来的 50 个连续剧。

并且选项 1 可以工作，即使序列本身是非数值但可以计算成数值。只是有点复杂。这就是为什么我说连续剧必须存在一个公式才能使该方法起作用。这是一个示例，您可以如何将 50 添加到序列号“ABC00000C”，从而使“ABC00001Y”成为序列号:

declare @from varchar(9) = 'ABC00000C';

declare @from_int bigint;

with cteSerialCharacters as (
    -- The set of characters used in a serial.
    -- As an example I've taken all number characters plus
    -- all capital letters from the alphabet excluding any 
    -- of these that are easily misread.
    select '0123456789ABCDEFGHJKLMNPRSTVWXYZ' as chars
),
cteNumberGenerator as (
    select cast (row_number() over (order by (select null)) as bigint) as n
    from ( select 1 union all select 1 union all select 1 union all select 1
            union all select 1 union all select 1 union all select 1 
            union all select 1 union all select 1
        ) t (xyz)
)
select 
    @from_int = sum(power(s.base, (n - 1)) * (-1 + charindex(substring(reverse(s.serial), n.n, 1), s.characterset)))
from (
        select 
            @from,
            cast(len(ch.chars) as bigint),
            ch.chars
        from cteSerialCharacters ch
    ) s (serial, base, characterset)
    inner join cteNumberGenerator n on (n.n <= len(s.serial));


select @from, @from_int;

declare @to varchar(9);
declare @to_int bigint;


select @to_int = @from_int + 50;


with cteSerialCharacters as (
    -- The set of characters used in a serial.
    -- As an example I've taken all number characters plus
    -- all capital letters from the alphabet excluding any 
    -- of these that are easily misread.
    select '0123456789ABCDEFGHJKLMNPRSTVWXYZ' as chars
),
cteNumberGenerator as (
    select cast (row_number() over (order by (select null)) as bigint) as n
    from ( select 1 union all select 1 union all select 1 union all select 1
            union all select 1 union all select 1 union all select 1 
            union all select 1 union all select 1
        ) t (xyz)
)
select 
    @to = (
            select 
                substring(s.characterset, 1 + (@to_int / power(s.base, n.n - 1)) % s.base, 1) as [text()]
            from (
                    select 
                        cast(len(ch.chars) as bigint),
                        ch.chars
                    from cteSerialCharacters ch
                ) s (base, characterset)
                cross join cteNumberGenerator n
            order by n.n desc
            for xml path(''), type
        ).value('text()[1]', 'varchar(9)')

select @to, @to_int;