SQL动态值（Ranking on dynamic values）排名查询实现方案

核心实现逻辑不依赖固定分组条数、固定层级，完全基于表内数据自动计算：

• 先为每条部门记录匹配对应的一级根部门：根部门是能作为当前部门名称前缀的、长度最短的部门记录
• 再根据部门名称的词段数量计算层级排名：根部门排名为1，每多一级后缀（名称中多一个空格分隔的词段）排名加1

适配SQL Server的实现代码如下：

WITH DeptWithRoot AS (
    SELECT
        curr.DeptNo,
        curr.DeptName,
        root_match.DeptName AS RootDept,
        LEN(curr.DeptName) - LEN(REPLACE(curr.DeptName, ' ', '')) + 1 AS CurrWordCnt,
        LEN(root_match.DeptName) - LEN(REPLACE(root_match.DeptName, ' ', '')) + 1 AS RootWordCnt
    FROM dbo.RankingTest curr
    CROSS APPLY (
        SELECT TOP 1 DeptName
        FROM dbo.RankingTest ref
        WHERE curr.DeptName LIKE ref.DeptName + '%'
        ORDER BY LEN(ref.DeptName) ASC
    ) root_match
)
SELECT
    DeptNo,
    DeptName,
    CurrWordCnt - RootWordCnt + 1 AS Ranking
FROM DeptWithRoot
ORDER BY DeptNo

方案说明 #

• 无硬编码规则，自动识别新增的一级部门、任意深度的层级部门，单组下不管有多少条记录都能正确计算排名
• 兼容根部门名称本身带空格的场景，比如后续新增名为Tech Center的一级部门，其下属子部门也能正确匹配归属、计算排名
• 执行返回的结果和预期完全一致：Sales/Purchase/HR三个根部门排名为1，带一级后缀的Internal/External类部门排名为2，带二级后缀的IND/APC/ASA类部门排名为3

内容的提问来源于stack exchange，提问作者Shruti