1

3

IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
colA VARCHAR(20),
colB VARCHAR(20)
)
INSERT INTO #tab
SELECT '2014062001','A' UNION ALL
SELECT '2014062001','B' UNION ALL
SELECT '2014062001','A' UNION ALL
SELECT '2014062002','B' UNION ALL
SELECT '2014062002','C' UNION ALL
SELECT '2014062003','A' UNION ALL
SELECT '2014062004','A' UNION ALL
SELECT '2014062004','D' UNION ALL
SELECT '2014062004','E' UNION ALL
SELECT '2014062005','A'
--测试数据结束
;WITH cte AS (
SELECT DISTINCT * FROM #tab
),cte2 AS (
SELECT a.colA,COUNT(a.colB) AS cnt FROM cte a
WHERE a.colB NOT IN (SELECT colB FROM cte b WHERE cast(a.colA AS int)>cast(b.colA AS int))
GROUP BY a.colA
),cte3 AS (
SELECT DISTINCT colA FROM #tab
),cte4 AS (
SELECT a.colA,isnull(b.cnt,0) AS cnt,ROW_NUMBER() OVER(ORDER BY CAST(a.colA AS INT)) AS rn FROM cte3 a
LEFT JOIN cte2 b ON a.colA=b.colA
),cte5 AS (
SELECT *,cnt AS sum_scnt FROM cte4 WHERE cola=(SELECT MIN(cola) FROM cte4)
UNION all
SELECT a.*,a.cnt+b.sum_scnt FROM cte4 a
INNER JOIN cte5 b ON a.rn=b.rn+1
)
SELECT colA,sum_scnt FROM cte5
colA sum_scnt
-------------------- -----------
2014062001 2
2014062002 3
2014062003 3
2014062004 5
2014062005 5