sql – 如何与“row_number()结束相反(按[Col]顺序分隔[Col])”

这是一个示例数据集(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() 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!