如何创建numAfter列统计后续orderLevel更大且p不同的记录数？

需求说明 #

需要为表中每条记录新增numAfter列，统计当前记录之后（即orderLevel字符串更大）且p值与当前记录不同的记录数量。最终目的是通过p和numAfter分组，将连续的p='------'的记录仅保留一条。

测试数据 #

预期结果 #

当前尝试代码 #

if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (p varchar(150), orderLevel varchar(150))
INSERT into #t1 VALUES 
   ('Jack', '.03'),
   ('Jack / Jill', '.03.01'),
   ('Jack / Jill / Bob', '.03.01.01'),
   ('------', '.03.01.05'),
   ('------', '.03.01.06'),
   ('Jack / Jill / Robert', '.03.01.11'),
   ('------', '.03.01.15'),
   ('Julie / Josh', '.04.02'),
   ('Julie / Josh / Tom', '.04.02.01'),
   ('Julie / Fred', '.04.03'),
   ('------', '.04.03.06'),
   ('------', '.04.03.07');


     select  t1.p,
             t1.orderLevel,
             count(t2.p) as numAfter
        from #t1 t1
        inner join #t1 t2
            on t2.orderLevel > t1.orderLevel
            and t1.p != t2.p
        group by t1.p, t1.orderLevel

解决方案 #

一、正确生成numAfter列的方法 #

你的自连接逻辑本身是正确的，但对于数据量较大的表，自连接性能会较差。可以改用以下两种更高效的方式：

方法1：相关子查询（逻辑直观，小表适用）

SELECT 
    p,
    orderLevel,
    (SELECT COUNT(*) FROM #t1 t2 WHERE t2.orderLevel > t1.orderLevel AND t2.p != t1.p) AS numAfter
FROM #t1 t1
ORDER BY orderLevel

方法2：窗口函数+子查询（大表性能更优）

先给记录按orderLevel排序，再统计后续符合条件的记录数：

WITH ranked_data AS (
    SELECT 
        p,
        orderLevel,
        ROW_NUMBER() OVER (ORDER BY orderLevel) AS rn
    FROM #t1
)
SELECT 
    p,
    orderLevel,
    (SELECT COUNT(*) FROM ranked_data rd2 WHERE rd2.rn > rd1.rn AND rd2.p != rd1.p) AS numAfter
FROM ranked_data rd1
ORDER BY orderLevel

二、替代方案：无需numAfter直接实现分隔符去重 #

如果核心需求只是去除连续的p='------'记录，完全不需要生成numAfter列，直接用窗口函数分组即可：

WITH group_cte AS (
    SELECT 
        *,
        -- 给非分隔符记录标记分组，连续的分隔符会被分到同一组
        SUM(CASE WHEN p != '------' THEN 1 ELSE 0 END) OVER (ORDER BY orderLevel ROWS UNBOUNDED PRECEDING) AS group_id
    FROM #t1
)
SELECT 
    p,
    orderLevel
FROM (
    SELECT 
        *,
        -- 同一组内的分隔符只保留第一条
        ROW_NUMBER() OVER (PARTITION BY group_id, p ORDER BY orderLevel) AS rn
    FROM group_cte
) t
WHERE p != '------' OR rn = 1
ORDER BY orderLevel

该方案通过group_id将连续的分隔符归为同一组，再通过ROW_NUMBER()只保留每组内的第一条分隔符，同时保留所有非分隔符记录。

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