求连续数字。。100分

[Landa_Jimmy]

create table tab_a
(
	from_a int,
	to_a int,
	name varchar(50)
)
insert into tab_a
select 1,5,'a' union all
select 6,12,'a' union all
select 13,15,'a' union all
select 18,20,'a' union all
select 22,35,'a' union all
select 40,45,'a' union all
select 46,50,'a' 

最终结果：
1 15 a
18 20 a
22 35 a
40 50 a

[许晨旭]

;WITH CTE AS (
SELECT
	ROW_NUMBER() OVER ( ORDER BY B.number)-B.number AS GROUPS,
	A.*,
	B.number
FROM #tab_a A
JOIN [master].dbo.spt_values B ON B.[type]='P' AND B.number BETWEEN A.from_a AND A.to_a
)
SELECT
	MIN(number) AS from_a,
	MAX(number) AS to_a
FROM CTE
GROUP BY GROUPS
ORDER BY 1

from_a      to_a
----------- -----------
1           15
18          20
22          35
40          50

(4 row(s) affected)

[道素]

另外一种用递归方式实现的例子：

WITH cte AS (
	SELECT *,ta.from_a AS pid
	  FROM tab_a AS ta WHERE NOT EXISTS (SELECT 0 FROM tab_a AS a WHERE a.name=ta.name AND a.to_a+1=ta.from_a)
	UNION ALL
	SELECT ta.*,c.pid
	  FROM tab_a AS ta INNER JOIN cte AS c ON ta.name=c.name AND ta.from_a=c.to_a+1
	)
	SELECT  MIN(from_a),MAX(to_a),name FROM cte GROUP BY pid,name

[Ginnnnnnnn]

;WITH CTE AS(		
SELECT *,
		ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY a.from_a) AS RowNr
	FROM #tab_a a
		WHERE NOT EXISTS(SELECT * FROM #tab_a WHERE name = a.name AND a.to_a + 1 = from_a)
		)
SELECT c.name,MIN(c.from_a),MAX(c.to_a)
	FROM CTE a
		LEFT JOIN CTE b ON a.RowNr -1 = b.RowNr
		INNER JOIN #tab_a c ON c.from_a > ISNULL(b.from_a,0) AND c.to_a <= a.to_a 
		GROUP BY a.RowNr,c.name