Sql 根据最大值为字母数字大小写分配数值

我们可以使用窗口函数MAX()和SUM()来实现这一点:

SELECT ID, Version, City,
          CASE WHEN  ISNUMERIC(Notes) = 0 
               THEN ISNULL(max_Notes, 0) + sum(step) OVER (PARTITION BY ID, Version ORDER BY City, rn)
               ELSE Notes
          END AS Notes
FROM (
  SELECT *, max(CASE WHEN ISNUMERIC(Notes)=1 THEN Notes END) OVER (PARTITION BY ID, Version) AS max_Notes,
            CASE WHEN  ISNUMERIC(Notes)=0 THEN 10 END AS step,
            ROW_NUMBER() OVER (PARTITION BY ID, Version ORDER BY City) AS rn
  FROM MYTABLE
) as s
ORDER BY ID, Version, Notes;

子查询将获得每个组中最高的音符，并 for each 字母数字值分配顺序步长，而外部查询将根据最大音符和按City(城市)排序的步长总和来计算每个字母数字行的音符( 如果希望将不同的值分配给同一组中的重复城市，还需要按行号进行排序.)

这是针对此数据集的:

INSERT INTO mytable VALUES
(101,   1,  'Sweden',   '01'),
(101,   1,  'Berlin',   '20'),
(101,   1,  'Tokyo',    'AAB'),
(101,   1,  'Delhi',    'C23'),
(101,   1,  'Durban',   '34'),
(101,   2,  'Berlin',   '0'),
(101,   2,  'Vatican',  'AB'),
(101,   2,  'Mexico',   '329'),
(101,   3,  'New york', 'AC'),
(101,   3,  'Berlin',   'AB'),
(101,   4,  'Paris',    'AC'),
(101,   4,  'Paris',    '25'),
(101,   4,  'Paris',    'AB');

结果:

ID  Version City    Notes
101 1   Sweden      1
101 1   Berlin      20
101 1   Durban      34
101 1   Delhi       44
101 1   Tokyo       54
101 2   Berlin      0
101 2   Mexico      329
101 2   Vatican     339
101 3   Berlin      10
101 3   New york    20
101 4   Paris       25
101 4   Paris       35
101 4   Paris       45

Demo here个