我试图在数据表中组合重复的条目,并给他们一个新的数字.
这是一个示例数据集(runnable copy)
declare @tmpTable table
(ID Varchar(1),First varchar(4),Last varchar(5),Phone varchar(13),NonKeyField varchar(4))
insert into @tmpTable select 'A','John','Smith','(555)555-1234','ASDF'
insert into @tmpTable select 'B','GHJK'
insert into @tmpTable select 'C','Jane','QWER'
insert into @tmpTable select 'D','RTYU'
insert into @tmpTable select 'E','Bill','Blake','(555)555-0000','BVNM'
insert into @tmpTable select 'F','%^&*'
insert into @tmpTable select 'G','!#RF'
select row_number() over (partition by First,Last,Phone order by ID) NewIDNum,*
from @tmpTable order by ID
现在它给了我结果
NewIDNum ID First Last Phone NonKeyField -------------------- ---- ----- ----- ------------- ----------- 1 A John Smith (555)555-1234 ASDF 2 B John Smith (555)555-1234 GHJK 1 C Jane Smith (555)555-1234 QWER 3 D John Smith (555)555-1234 RTYU 1 E Bill Blake (555)555-0000 BVNM 2 F Bill Blake (555)555-0000 %^&* 4 G John Smith (555)555-1234 !#RF
然而,这与我想要的相反,NewIDNum会在找到密钥的新组合时重置其计数器.我希望所有相同的组合具有相同的ID.所以如果它按照我想要的方式行事,我会得到以下结果
NewIDNum ID First Last Phone NonKeyField -------------------- ---- ----- ----- ------------- ----------- 1 A John Smith (555)555-1234 ASDF 1 B John Smith (555)555-1234 GHJK 2 C Jane Smith (555)555-1234 QWER 1 D John Smith (555)555-1234 RTYU 3 E Bill Blake (555)555-0000 BVNM 3 F Bill Blake (555)555-0000 %^&* 1 G John Smith (555)555-1234 !#RF
获得我想要的结果的正确方法是什么？
我没有在原始帖子中包含此要求：如果添加更多行,我需要NewIDNum在此查询的后续运行中为现有行生成相同的数字(假设所有新行将具有更高的ID“值”,如果a order by在ID列上完成)
因此,如果在后一天完成以下操作
insert into @tmpTable select 'H','4321' insert into @tmpTable select 'I','Jake','Jons','1234' insert into @tmpTable select 'J','2345'
再次运行正确的查询会给
NewIDNum ID First Last Phone NonKeyField -------------------- ---- ----- ----- ------------- ----------- 1 A John Smith (555)555-1234 ASDF 1 B John Smith (555)555-1234 GHJK 2 C Jane Smith (555)555-1234 QWER 1 D John Smith (555)555-1234 RTYU 3 E Bill Blake (555)555-0000 BVNM 3 F Bill Blake (555)555-0000 %^&* 1 G John Smith (555)555-1234 !#RF 1 H John Smith (555)555-1234 4321 4 I Jake Jons (555)555-1234 1234 1 J John Smith (555)555-1234 2345
解决方法
你可以使用dense_rank()：
dense_rank() over (order by First,Phone) as NewIDNum
在回复您的评论时,您可以使用相同的(First,Phone)组合对每组行的旧Id列的最小值进行排序：
select *
from (
select dense_rank() over (order by min_id) as new_id,*
from (
select min(id) over (
partition by First,Phone) as min_id,*
from @tmpTable
) as sub1
) as sub3
order by
new_id
