--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ColumnA] INT)
INSERT [tb]
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 6
--------------开始查询--------------------------
;WITH t1 AS 
(
    SELECT *,id1=ROW_NUMBER()OVER(ORDER BY GETDATE()) FROM tb 
),t2 AS 
(
    SELECT *,id2=(SELECT ISNULL(max(id1),1) FROM t1 AS b WHERE b.[ColumnA] IS NOT NULL AND  b.id1<=a.id1
        AND EXISTS(SELECT 1 FROM t1  WHERE [ColumnA]  IS NOT NULL AND id1=b.id1-1) 
    ) 
    FROM t1 AS a
)
SELECT [ColumnA],[ColumnB]=DENSE_RANK()OVER( ORDER BY id2) FROM t2   
----------------结果----------------------------
/* 
ColumnA    ColumnB
1    1
NULL    1
NULL    1
NULL    1
NULL    1
NULL    1
NULL    1
NULL    1
2    1
3    2
NULL    2
NULL    2
NULL    2
NULL    2
NULL    2
NULL    2
NULL    2
NULL    2
4    2
5    3
NULL    3
NULL    3
NULL    3
NULL    3
NULL    3
NULL    3
NULL    3
NULL    3
6    3
*/

------解决方案--------------------
探讨


引用:

SQL code
-->  SELECT *,id2=(SELECT ISNULL(max(id1),1) FROM t1 AS b WHERE b.[ColumnA] IS NOT NULL AND  b.id1<=a.id1
AND EXISTS(SELECT 1 FROM t1  WHERE [ColumnA]  IS NOT NULL AND id1=……