T-SQL从Alpha数字键中计算最大值

Question

我有一个客户表，该表具有一个由5个字母和3个数字组成的字母数字键。

我试图为每个5个字母按顺序计算下3位数字：

示例键

ALPHA001
ALPHA002
NUMBE001
NUMBE002
NUMBE003
PREST001
PREST002
PREST003
PREST004
PREST005

从上面的键列表中，我想返回每个唯一5个字母键的最大值。即。

返回的值

ALPHA002
NUMBE003
PREST005

Answer 1

首先：不要在一列中存储多个值。应将键和运行号存储在单独的列中，并将它们组合起来只是为了显示目的...

尝试这个

DECLARE @mockupTable TABLE(ID INT IDENTITY,YourKey VARCHAR(100));
INSERT INTO @mockupTable VALUES
 ('ALPHA001')
,('ALPHA002')
,('NUMBE001')
,('NUMBE002')
,('NUMBE003')
,('PREST001')
,('PREST002')
,('PREST003')
,('PREST004')
,('PREST005');
WITH cte AS
(
SELECT *
      ,ROW_NUMBER() OVER(PARTITION BY LEFT(YourKey,5) ORDER BY CAST(RIGHT(YourKey,3) AS INT) DESC) AS PartitionedRowNumber 
FROM @mockupTable
)
SELECT *
FROM cte
WHERE PartitionedRowNumber =1;

结果

ID  Key
2   ALPHA002
5   NUMBE003
10  PREST005

Answer 2

您可以使用row_number()：

select top (1) with ties t.*
from table t
order by row_number() over (partition by left(col, patindex('%[0-9]%', col)) order by col desc);

如果固定了字母，则只需使用left()：

order by row_number() over (partition by left(col, 5) order by col desc);

Answer 3

我试图为每个5分按顺序计算下3位数字 信件

这应该做到：

SELECT CONCAT(LEFT(k, 5), FORMAT(MAX(RIGHT(k, 3)) + 1, '000'))
FROM (VALUES
    ('ALPHA001'),
    ('ALPHA002'),
    ('NUMBE001'),
    ('NUMBE002'),
    ('NUMBE003'),
    ('PREST001'),
    ('PREST002'),
    ('PREST003'),
    ('PREST004'),
    ('PREST005')
) tests(k)
GROUP BY LEFT(k, 5)

Answer 4

您可以使用GROUP BY和MAX：

进行此操作

SELECT  KeyPrefix = LEFT(ExampleKey, 5),
        NextKey = CONCAT(LEFT(ExampleKey, 5), 
                        RIGHT(CONCAT('000', MAX(CONVERT(INT, RIGHT(ExampleKey, 3))) + 1), 3))
FROM    (VALUES
            ('ALPHA001'), ('ALPHA002'), ('NUMBE001'), ('NUMBE002'), ('NUMBE003'), 
            ('PREST001'), ('PREST002'), ('PREST003'), ('PREST004'), ('PREST005')
        ) t (ExampleKey)
GROUP BY LEFT(ExampleKey, 5);

关键操作是：

1. 获取密钥的数字部分：RIGHT(ExampleKey, 3)
2. 将其转换为整数：CONVERT(INT, <output from 1>)
3. 找到密钥类型的最大值，然后添加1： MAX(<output from 2>) + 1
4. 与零一起使用：RIGHT(CONCAT('000', MAX(<output from 3>), 3)
5. 与原始前缀相连：CONCAT(LEFT(ExampleKey, 5), <output from 4>)

我强烈建议将其存储在两列中，然后使用计算的列结合：

CREATE TABLE dbo.T
(
    KeyPrefix CHAR(5) NOT NULL,
    KeySequence INT NOT NULL,
    TKey AS CONCAT(KeyPrefix, RIGHT(CONCAT('000', KeySequence), 3))
);

那么您的查询变得更简单：

SELECT  KeyPrefix, 
        KeySequence = MAX(KeySequence) + 1,
        TKey = CONCAT(KeyPrefix, RIGHT(CONCAT('000', MAX(KeySequence) + 1), 3))
FROM    (VALUES
            ('ALPHA', 1), ('ALPHA', 2), ('NUMBE', 1), ('NUMBE', 2), ('NUMBE', 3), 
            ('PREST', 1), ('PREST', 2), ('PREST', 3), ('PREST', 4), ('PREST', 5)
        ) t (KeyPrefix, KeySequence)
GROUP BY KeyPrefix;

尽管值得注意，您实际上永远不需要像我在TKey列中所做的那样重建密钥，但您只需要最大的密钥序列。

Answer 5

使用此查询。

        GO
        ;WITH cte AS
        (
        SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(YourKey,patindex('%[0-9]%', YourKey)) ORDER BY CAST(RIGHT(YourKey,patindex('%[A-Z]%', YourKey)) AS INT) DESC) AS rr , YourKey FROM @mockupTable
        )
        SELECT YourKey FROM cte WHERE rr =1;
        GO