急求解决办法: 怎么将相邻的两个间隔行记录， 统一成一个标识

急求解决方法: 如何将相邻的两个间隔行记录， 统一成一个标识
如,数据表结果如下,只有一列:
ColumnA
1
NULL
NULL
NULL
NULL
NULL
NULL
NULL
2
3
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
4
5
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
6

非空的记录是按照从1开始递增的形式增长的, 现在要求新增一列column B, 将相邻的不为空的记录行之间标记为一个同一标识，结果如下（统计的是标识网站session的,标识结果随意，只要能区分就行） :  


ColumnA ColumnB
1 Mark1  
NULL Mark1  
NULL Mark1  
NULL Mark1  
NULL Mark1  
NULL Mark1 
NULL Mark1 
NULL Mark1 
2 Mark1 
3 Mark2 
NULL Mark2 
NULL Mark2
NULL Mark2
NULL Mark2
NULL Mark2
NULL Mark2
NULL Mark2
NULL Mark2
4 Mark2
5 Mark3
NULL Mark3
NULL Mark3
NULL Mark3
NULL Mark3
NULL Mark3
NULL Mark3
NULL Mark3
NULL Mark3
6 Mark3

急求高手指教，感激不尽.!





 

[解决办法]

SQL code

--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([ColumnA] INT)INSERT [tb]SELECT 1 UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT NULL UNION ALLSELECT 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    ColumnB1    1NULL    1NULL    1NULL    1NULL    1NULL    1NULL    1NULL    12    13    2NULL    2NULL    2NULL    2NULL    2NULL    2NULL    2NULL    2NULL    24    25    3NULL    3NULL    3NULL    3NULL    3NULL    3NULL    3NULL    3NULL    36    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=……