sql-server - SQL Server row_number() 按查询分区

Question

我的数据采用以下格式，并尝试使用 row_number() 和 accountCategoryount 获取唯一的 customerIDCount 和分区，但遇到以下查询问题，任何人都可以帮忙吗？

with cte as 
(
    select 
        *,
        accountCategoryCount = row_number() over (partition by phoneNUmber, ID, accountCategory Order by phoneNumber) 
    from 
        (select distinct * from myTable) base
),
cet2 as 
(
    select 
        *,
        customerIDCount = row_number() over (partition by phoneNumber, ID order by phoneNumber 
    from 
        cte A
)
select * from cte2

我的表数据:

phoneNumber ID   name  dob      accountCategory accountNumber balance
123456789   456  sam   10061970 checking        4567          10
123456789   456  sam   10061970 checking        4568          200
123456789   456  sam   10061970 Savings         4569          12
123456789   456  sam   10061970 Savings         4570          13
123456789   789  John  10101970 CreditCard      4571          400
123456789   789  John  10101970 CreditCard      4572          600
123456789   789  John  10101970 Savings         4573          50

预期结果

phoneNumber ID   name  dob      accountCategory accountNumber balance accountCategoryCount customerIDCount 
123456789   456  sam   10061970 checking        4567          10      1                    1
123456789   456  sam   10061970 checking        4568          200     2                    1
123456789   456  sam   10061970 Savings         4569          12      1                    1
123456789   456  sam   10061970 Savings         4570          13      2                    1
123456789   789  John  10101970 CreditCard      4571          400     1                    2
123456789   789  John  10101970 CreditCard      4572          600     2                    2
123456789   789  John  10101970 Savings         4573          50      1                    2

Answer 1

问题是 ROW_NUMBER() 总是返回不同的号码，而您希望为“phoneNumber”和“ID”的相同值获取相同的号码，为此您需要使用 DENSE_RANK()对于关系返回相同的值:

with cte as (
    select *,
        row_number() over (partition by phoneNumber, ID, accountCategory Order by phoneNumber) as accountCategoryCount,
        dense_rank() over (order by phoneNumber, ID) as customerIDCount
    from 
        (select distinct * from myTable) base
)
select * from cte

结果:

| phoneNumber |  ID | accountCategory | ... | accountCategoryCount | customerIDCount |
|-------------|-----|-----------------|-----|----------------------|-----------------|
|   123456789 | 456 |        checking |     |                    1 |               1 |
|   123456789 | 456 |        checking |     |                    2 |               1 |
|   123456789 | 456 |         Savings |     |                    1 |               1 |
|   123456789 | 456 |         Savings |     |                    2 |               1 |
|   123456789 | 789 |      CreditCard |     |                    1 |               2 |
|   123456789 | 789 |      CreditCard |     |                    2 |               2 |
|   123456789 | 789 |         Savings |     |                    1 |               2 |