日期:2014-05-17 浏览次数:20455 次
USE tempdb
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test;
GO
CREATE TABLE test
(
id int
);
GO
INSERT INTO test VALUES
(1),(2),(3),(5),(8),(9),(10),(11);
GO
-------------------------------------
-------------------------------------
/*
id_star id_end
------- -------
1 3
8 11
*/
SELECT MIN(id) AS start_range ,
MAX(id) AS end_range
FROM ( SELECT id ,
( SELECT MIN(id)
FROM Test AS b
WHERE b.id >= a.id
AND NOT EXISTS ( SELECT 1
FROM test AS c
WHERE b.id = c.id - 1 )
) AS grp
FROM test AS a
) AS d
GROUP BY grp
HAVING MIN(id)<>MAX(id)
/*
start_range end_range
1 3
8 11*/
--2005 往上版本可用这个
SELECT MIN(id) AS start_range,MAX(id) AS end_range
FROM (SELECT id,id -ROW_NUMBER() OVER(ORDER BY id) AS grp FROM test ) AS d
GROUP BY grp
HAVING MIN(id)<>MAX(id)
--确实范围和现有范围(也称间断和孤岛问题)
--1、缺失范围(间断)
/*
收集人:TravyLee
时间:2012-03-25
如有引用)
---------------------------------
间断问题的解决方案1;使用子查询
step 1:找到间断之前的值,每个值增加一个间隔
step 2:对于没一个间断的起点,找出序列中现有得值,再减去一个间隔
本人理解为:找到原数据表中的值加一减一是否存在,若有不妥,望纠正
生成测试数据:
go
if object_id('tbl')is not null