高难度计算：计算最后一次离最近一次出现的NULL值连续有几个

原贴：http://topic.csdn.net/u/20100414/18/329cdadd-dffe-407b-9203-470b3ef133d8.html?seed=956126794

 

/*

现有表TA 如下，ZA只能输入1或NULL,ZB只能输入2或NULL,ZC只能输入3或NULL,ZD只能输入4或NULL,
null是空值

id ZA ZB ZC ZD
1 1 2 3 null
2 null 2 3 4
3 null null 3 null  
4 null 2 null null  
 
要求计算出最后一次，对应字段出现离上次（含本次）有几次，比如上面的第四条记录中：

1离上次有3次没出现，2离上次有1次没出现，3离上次有1次没出现，4离上次有2次没出现

求SQL语句写法
*/

 

--表结构经添加数据

DECLARE @A TABLE(ID INT,ZA INT,ZB INT,ZC INT,ZD INT)

INSERT @A SELECT 1, 1,    2 ,   3, NULL

UNION ALL SELECT 3, NULL, 2,    3, 4

UNION ALL SELECT 5, NULL, NULL, 3, NULL

UNION ALL SELECT 7, NULL, NULL, 3, NULL

UNION ALL SELECT 10, NULL, 2, NULL, NULL

UNION ALL SELECT 18, NULL, 2,   3, NULL

--SELECT * FROM @A

 

SELECT 'ZA' 项目,SUM(CASE WHEN ZA IS NULL THEN 1 ELSE 0 END) 次数

FROM @A

WHERE ID>CASE WHEN (SELECT MAX(ID) FROM @A WHERE ZA IS NOT NULL)

                  =(SELECT MAX(ID) FROM @A)

             THEN (SELECT MAX(ID) FROM @A WHERE ZA IS NOT NULL

                  AND ID<(SELECT MAX(ID) FROM @A WHERE ZC IS NULL))

             ELSE (SELECT MAX(ID) FROM @A WHERE ZA IS NOT NULL)

        END

UNION ALL

SELECT 'ZB',SUM(CASE WHEN ZB IS NULL THEN 1 ELSE 0 END)

FROM @A

WHERE ID>CASE WHEN (SELECT MAX(ID) FROM @A WHERE ZB IS NOT NULL)

                  =(SELECT MAX(ID) FROM @A)

             THEN (SELECT MAX(ID) FROM @A WHERE ZB IS NOT NULL

                  AND ID<(SELECT MAX(ID) FROM @A WHERE ZB IS NULL))

             ELSE (SELECT MAX(ID) FROM @A WHERE ZB IS NOT NULL)

        END

UNION ALL

SELECT 'ZC',SUM(CASE WHEN ZC IS NULL THEN 1 ELSE 0 END)

FROM @A

WHERE ID>CASE WHEN (SELECT MAX(ID) FROM @A WHERE ZC IS NOT NULL)

                  =(SELECT MAX(ID) FROM @A)

             THEN (SELECT MAX(ID) FROM @A WHERE ZC IS NOT NULL

                  AND ID<(SELECT MAX(ID) FROM @A WHERE ZC IS NULL))

             ELSE (SELECT MAX(ID) FROM @A WHERE ZC IS NOT NULL)

        END

UNION ALL

SELECT 'ZD',SUM(CASE WHEN ZD IS NULL THEN 1 ELSE 0 END)

FROM @A

WHERE ID>CASE WHEN (SELECT MAX(ID) FROM @A WHERE ZD IS NOT NULL)

                  =(SELECT MAX(ID) FROM @A)

             THEN (SELECT MAX(ID) FROM @A WHERE ZD IS NOT NULL

                  AND ID<(SELECT MAX(ID) FROM @A WHERE ZD IS NULL))

             ELSE (SELECT MAX(ID) FROM @A WHERE ZD IS NOT NULL)

        END

 

/*

项目  次数

---- -----------

ZA   5

ZB   2

ZC   1

ZD   4

 

(4 行受影响)

*/