求每行相邻值相同的范围区间
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
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)
/*
rangeb
1-6sa
9-9b
13-16c
19-19sa
*/
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
------------------
16sa
99b
1316c
1919sa
