我正在尝试合并数据表中的重复条目并给它们一个新数字。
下面是一个示例数据集(可运行的副本)
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', 'John', 'Smith', '(555)555-1234', 'GHJK'
insert into @tmpTable select 'C', 'Jane', 'Smith', '(555)555-1234', 'QWER'
insert into @tmpTable select 'D', 'John', 'Smith', '(555)555-1234', 'RTYU'
insert into @tmpTable select 'E', 'Bill', 'Blake', '(555)555-0000', 'BVNM'
insert into @tmpTable select 'F', 'Bill', 'Blake', '(555)555-0000', '%^&*'
insert into @tmpTable select 'G', 'John', 'Smith', '(555)555-1234', '!#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 "值"如果在 ID 列上完成排序)
因此,如果在以后的日期完成了以下操作
insert into @tmpTable select 'H', 'John', 'Smith', '(555)555-1234', '4321'
insert into @tmpTable select 'I', 'Jake', 'Jons', '(555)555-1234', '1234'
insert into @tmpTable select 'J', 'John', 'Smith', '(555)555-1234', '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, Last, Phone) as NewIDNum
为了响应您的评论,您可以使用相同的(First, Last, Phone)
组合对每组行的旧Id
列的最小值进行排序:
select *
from (
select dense_rank() over (order by min_id) as new_id
, *
from (
select min(id) over (
partition by First, Last, Phone) as min_id
, *
from @tmpTable
) as sub1
) as sub3
order by
new_id
建立在@Andomar的原始答案之上——这将适用于您更新的需求(尽管这不太可能很好地扩展)
select
DENSE_RANK() over (ORDER BY IdRank, First, Last, Phone) AS NewIDNum,
ID,
First,
Last,
Phone,
NonKeyField
from
(
select
MIN(ID) OVER (PARTITION BY First, Last, Phone) as IdRank,
*
from
@tmpTable
) as x
order by
ID;
感谢安多玛的回答作为起点,我自己解决了
select sub1.rn, tt.*
from @tmpTable tt
inner join (
select row_number() over (order by min(ID)) as rn, first, last, phone
from @tmpTable
group by first, last, phone
) as sub1 on tt.first = sub1.first and tt.last = sub1.last and tt.phone = sub1.phone
这会产生
rn ID First Last Phone NonKeyField
-------------------- ---- ----- ----- ------------- -----------
1 A John Smith (555)555-1234 ASDF
1 B John Smith (555)555-1234 GHJK
1 D John Smith (555)555-1234 RTYU
1 G John Smith (555)555-1234 !#RF
1 H John Smith (555)555-1234 4321
1 J John Smith (555)555-1234 2345
2 C Jane Smith (555)555-1234 QWER
3 E Bill Blake (555)555-0000 BVNM
3 F Bill Blake (555)555-0000 %^&*
4 I Jake Jons (555)555-1234 1234
查看SQL执行计划,Adnomar的答案对于比我更大的数据集运行得更快。(53% 的执行时间与 47% 的执行时间,当彼此相邻运行并选中"包括实际执行计划"时。
这应该有效
select dense_rank() over (order by First, Last, Phone) NewIDNum, *
from @tmpTable order by ID