求每行相邻值相同的范围区间

[qqzeng-ip]

CREATE TABLE #tbTest (a bigint ,b varchar(100))
INSERT #tbTest
SELECT 1,'sa' UNION all
SELECT 3,'sa' UNION ALL
SELECT 6,'sa' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 13,'c' UNION ALL
SELECT 16,'c' UNION ALL
SELECT 19,'sa'

SELECT * FROM #tbTest

1 sa
3 sa
6 sa
9 b
13 c
16 c
19 sa

已经 排好序的了

结果：
1-6 sa
9-9 b
12-16 c
19-19 sa

[Shawn]

DROP TABLE #tbTest
go

CREATE TABLE #tbTest (a bigint ,b varchar(100))
INSERT #tbTest
SELECT 1,'sa' UNION all
SELECT 3,'sa' UNION ALL
SELECT 6,'sa' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 13,'c' UNION ALL
SELECT 16,'c' UNION ALL
SELECT 19,'sa'

;WITH CTE AS
(
	SELECT *,rowid = ROW_NUMBER() OVER(ORDER BY a) FROM #tbTest
),
cte1 AS
(
	SELECT *, gp = rowid-ROW_NUMBER() OVER(PARTITION BY b ORDER BY a) FROM cte
)
SELECT 
	[range]=LTRIM(MIN(a))+'-'+LTRIM(MAX(a)), b
FROM cte1
GROUP BY gp, b
ORDER BY MIN(rowid)

/*
range	b
1-6	sa
9-9	b
13-16	c
19-19	sa
*/

[中关村网名]

SELECT MIN(t1.a) AS  mi,MAX(t1.a) AS ma, t1.b
FROM #tbTest AS t1
	LEFT JOIN #tbTest t2
	ON t2.a = (
			SELECT MIN(a)
			FROM #tbTest
			WHERE t1.b <> b
				AND a > t1.a)
GROUP BY t1.b,t2.b
ORDER BY mi
------------------
1	6	sa
9	9	b
13	16	c
19	19	sa

[sqlkxr]

在sql2000下，如果是非连续的，很简单，

[qqzeng-ip]

如果 用 sql2012 新函数 LAG LEAD 怎么来呢

[Shawn]

引用 4 楼 zengzhan 的回复:

如果 用 sql2012 新函数 LAG LEAD 怎么来呢

直接用LAG,LEAD达不到效果。