如何通过分配序号来重命名项目? [英] How to rename items by assigning sequential number?

问题描述

我有下表:

DECLARE @OperatorPrice TABLE 
(
    ID INT NOT NULL, DealerId INT NULL, DealerName VARCHAR(50) NULL
)

和示例数据:

INSERT INTO @OperatorPrice 
(
    ID, DealerId, DealerName
)
VALUES
  (226, 1,  'WestCarDealer')
, (112, 1,  'WestCarDealer')
, (266, 2, 'AO')
, (112, 2, 'AO')
, (93,  3, 'Best on the West')
, (93,  3, 'Best on the West')

我想要的是将所有经销商重命名为'Dealer1'，'Dealer2'，'Dealer3'.该数字应按升序分配:AO应该重命名为'Dealer1'，因为AO以A开头，Best on the West应该重命名为Dealer2，因为它以B，WestCarDealer开头应该重命名为Dealer3，因为它以W开头.

What I want is to rename all dealers to 'Dealer1', 'Dealer2', 'Dealer3'. The number should be assigned in ascending order: AO should be renamed to 'Dealer1' cause AO starts with A, Best on the West should be renamed to Dealer2 cause it starts with B, WestCarDealer should be renamed to Dealer3 cause it starts with W.

因此所需的输出应如下所示:

So the desired output should looks like this:

  (226, 1, 'Dealer3')
, (112, 1, 'Dealer3')
, (266, 2, 'Dealer1')
, (112, 2, 'Dealer1')
, (93,  3, 'Dealer2')
, (93,  3, 'Dealer2')

唯一的DealerName的大约数量是50个经销商.

Approximate quantity of unique DealerName's is 50 dealers.

如何以这种方式重命名汽车经销商?

How can I rename car dealers in that way?

我曾尝试使用游标来实现此目的，但无法存储要递增的数字.

I've tried to use cursors for this purpose, but could not store the number to increment.

推荐答案

您可以使用CTE更新表变量

You can use an CTE to update the table variable

示例

;with cte as (
Select *
      ,NewVal = concat('Dealer',dense_rank() over ( order by DealerName))
 From  @OperatorPrice
)
Update cte Set DealerName = NewVal

更新了@OperatorPrice

ID  DealerId    DealerName
226 1           Dealer3
112 1           Dealer3
266 2           Dealer1
112 2           Dealer1
93  3           Dealer2
93  3           Dealer2

这篇关于如何通过分配序号来重命名项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！