求每行紧邻值相同的范围区间
日期：2014-05-17 浏览次数：20383 次
求每行相邻值相同的范围区间
------解决方案--------------------
------解决方案--------------------
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)
/*
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
