DENSE_RANK函数未按预期工作，求SQL查询解决方案

问题根源 #

你的查询中DENSE_RANK() OVER(ORDER BY mu.MacAddress)是按Mac地址的字符串字典序排名，而非你需要的「MacAddress重复次数」，这是导致排名不符合预期的核心原因。此外，当前的GROUP BY逻辑是将同一Mac地址按用户拆分分组，统计的是每个用户对应该Mac地址的记录数，而非该Mac地址的总重复次数——如果你的需求是统计Mac地址的全局重复次数，这部分也需要调整。

解决方案 #

根据你的实际需求，提供两种针对性方案：

方案1：按Mac地址的全局重复次数排名 #

如果需要统计每个MacAddress在整张表中的总重复次数，并以此排名，先通过子查询/CTE计算Mac地址的总次数，再关联其他表获取用户信息：

WITH MacTotalCounts AS (
    SELECT 
        MacAddress,
        COUNT(*) AS TotalQuantity
    FROM MacsUsers
    GROUP BY MacAddress
)
SELECT
    MTC.TotalQuantity AS Quantity,
    U.Name,
    U.SurName,
    MU.MacAddress,
    DENSE_RANK() OVER(ORDER BY MTC.TotalQuantity DESC) AS RNK
FROM MacsUsers MU
JOIN Macs MAC ON MAC.MacAddress = MU.MacAddress
JOIN Users U ON MAC.UserEmail = U.Email
JOIN Profiles PROFILE ON PROFILE.MacAddress = MAC.MacAddress
JOIN MacTotalCounts MTC ON MTC.MacAddress = MU.MacAddress
GROUP BY MTC.TotalQuantity, U.Name, U.SurName, MU.MacAddress
ORDER BY RNK

方案2：按用户对应Mac地址的重复次数排名 #

如果你的需求是统计每个用户使用某Mac地址的次数，并以此排名，只需修改DENSE_RANK的排序字段为聚合后的Quantity（并按降序排列，让次数多的排在前面）：

SELECT
    COUNT(MU.MacAddress) AS Quantity,
    [USER].Name,
    [USER].SurName,
    MU.MacAddress,
    DENSE_RANK() OVER(ORDER BY COUNT(MU.MacAddress) DESC) AS RNK
FROM MacsUsers [MU]
JOIN Macs [MAC] ON [MAC].MacAddress = [MU].MacAddress
JOIN Users [USER] ON [MAC].UserEmail = [USER].Email
JOIN Profiles [PROFILE] ON [PROFILE].MacAddress = [MAC].MacAddress
GROUP BY MU.MacAddress, [USER].Name, [USER].SurName
ORDER BY RNK

关键说明 #

• DENSE_RANK()的排序字段必须是你用来排名的核心指标（即重复次数），而非Mac地址本身；
• 添加DESC可以让重复次数多的记录排名更靠前，符合常规的排名需求；
• 方案1的CTE确保了Mac地址的重复次数是全局统计的，不会被用户维度拆分。

内容的提问来源于stack exchange，提问作者Andres Guillen Hernandez