sql-server - 基于 "Group By"列运行 "Flip"序数计数器

Question

通常我很擅长解决基于集合的 tsql 问题。但这一个打败了我。我花了 3 天的时间将 while 循环过程转换为基于集合的过程。我已经到达下面的点了......但无法进行最后的跳跃。

我有以下几行。 MyOrdinal 将“按顺序”...第二列 (MyMarker) 将在具有值和为空之间交替。每当 MyMarker 上发生这种“翻转”时，我想将“group by”序数计数器增加 1。每当“翻转”值非空或空时，它们就会被分组为一个集合。

我已经尝试了几种方法，但它太难看了，无法发布。自从转向 ORM 以来，我不再在 tsql 上花费太多时间。

declare @Holder table (   MyOrdinal int not null , MyMarker int , MyGroupNumber int   )

INSERT INTO @Holder (MyOrdinal, MyMarker)
Select 1 , 1 
union all Select 2, 2
union all Select 3, null
union all Select 4, 3
union all Select 5, 4
union all Select 6, 5
union all Select 7, 6
union all Select 8, 7
union all Select 9, 8
union all Select 10, 9
union all Select 11, 10
union all Select 12, 11
union all Select 13, 12
union all Select 14, 13
union all Select 15, 14
union all Select 16, 15
union all Select 17, null
union all Select 18, null
union all Select 19, null
union all Select 20, 16
union all Select 21, 17
union all Select 22, 18
union all Select 23, null
union all Select 24, null
union all Select 25, 19
union all Select 26, 20
union all Select 27, null
union all Select 28, 21

Select * from @Holder

期望的输出

| MyOrdinal | MyMarker | MyGroupNumber |
|-----------|----------|---------------|
|         1 |        1 |             1 |
|         2 |        2 |             1 |
|         3 |    null  |             2 |
|         4 |        3 |             3 |
|         5 |        4 |             3 |
|         6 |        5 |             3 |
|         7 |        6 |             3 |
|         8 |        7 |             3 |
|         9 |        8 |             3 |
|        10 |        9 |             3 |
|        11 |       10 |             3 |
|        12 |       11 |             3 |
|        13 |       12 |             3 |
|        14 |       13 |             3 |
|        15 |       14 |             3 |
|        16 |       15 |             3 |
|        17 |    null  |             4 |
|        18 |    null  |             4 |
|        19 |    null  |             4 |
|        20 |       16 |             5 |
|        21 |       17 |             5 |
|        22 |       18 |             5 |
|        23 |    null  |             6 |
|        24 |    null  |             6 |
|        25 |       19 |             7 |
|        26 |       20 |             7 |
|        27 |    null  |             8 |
|        28 |       21 |             9 |

Answer 1

试试这个:

首先，这会为连续的非NULL MyMarker 分配相同的ROW_NUMBER。对于 NULL MyMarker 来说，ROW_NUMBER 为 NULL。之后，您需要为 NULL MyMarker 添加一个 ROW_NUMBER，使得该值介于之前的 NON-NULL 之间code> 和下一个非 NULL。然后使用DENSE_RANK最终分配MyGroupNumber:

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(ORDER BY MyOrdinal) - MyMarker + 1
    FROM @Holder
),
CteApply AS(
    SELECT
        t.MyOrdinal,
        t.MyMarker,
        MyGroupNumber = 
            CASE
                WHEN RN IS NULL THEN x.NewRN
                ELSE RN
            END
    FROM Cte t
    OUTER APPLY(
        SELECT TOP 1 RN * 1.1 AS NewRN
        FROM Cte
        WHERE 
            t.MyOrdinal > MyOrdinal
            AND MyMarker IS NOT NULL
        ORDER BY MyOrdinal DESC
    )x
)
SELECT 
    MyOrdinal,
    MyMarker,
    MyGroupNumber = DENSE_RANK() OVER(ORDER BY MyGroupNumber)
FROM CteApply