- --测试数据
- 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
- --*/
断号查询
最新推荐文章于 2024-03-06 16:35:16 发布