-------------------------断号查询-------------------------------------

有关断号查询的在论坛里面问的实在是太多了 在这里总结一下：

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
	end_col2=(
		SELECT MIN(col2) FROM tb aa
		WHERE col1=a.col1 AND col2>a.col2 
			AND NOT EXISTS(
				SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
		-1
FROM(
	SELECT col1,col2 FROM tb
	UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
	SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
	AND NOT EXISTS(
		SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/


--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
	end_col2=(
		SELECT MIN(col2) FROM tb aa
		WHERE col1=a.col1 AND col2>a.col2 
			AND NOT EXISTS(
				SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
		-1
FROM(
	SELECT col1,col2 FROM tb
	UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
	SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
	AND NOT EXISTS(
		SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/
 
让你望见影子<trieagle@126.com>  9:49:01
--生成已用编号分布字符串的函数
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @re varchar(8000),@pid int
	SELECT @re='',@pid=-1
	SELECT @re=CASE 
			WHEN col2=@pid+1 THEN @re
			ELSE @re
				+CASE 
					WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
					ELSE CAST(-@pid as varchar)
				END
				+','+CAST(col2 as varchar) 
			END,
		@pid=col2
	FROM tb
	WHERE col1=@col1
	ORDER BY col2
	RETURN(STUFF(@re,1,2,'')
		+CASE 
			WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
			ELSE CAST(-@pid as varchar)
		END)
END
GO

--生成缺号分布字符串的函数
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @re varchar(8000),@pid int
	SELECT @re='',@pid=0
	SELECT @re=CASE 
			WHEN col2=@pid+1 THEN @re
			ELSE @re+','+CAST(@pid+1 as varchar)
				+CASE 
					WHEN @pid+1=col2-1 THEN ''
					ELSE CAST(1-col2 as varchar)
				END
			END,
		@pid=col2
	FROM tb
	WHERE col1=@col1
	ORDER BY col2
	RETURN(STUFF(@re,1,1,''))
END
GO

--调用测试
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7

--查询
SELECT col1,
	col2_Series=dbo.f_GetStrSeries(col1),
	col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--结果
col1       col2_Series       col2_Series 
-------------- ------------------------ --------------
a          2-3,5,8-9        1,4,6-7
b          1,5-7           2-4
--*/