找到序列中的连续号码

--王成辉原创，转贴请注明出自微软BI开拓者www.windbi.com


用下面的格式显示：
1-3
5-6
8-10
12
15-16
18-19
………………
--产生测试数据
create table test(id int)
insert into test select 5
union all select 1
union all select 2
union all select 3
union all select 6
union all select 8
union all select 9
union all select 10
union all select 12
union all select 15
union all select 16
union all select 18
union all select 19
--解决方法
select case when min(t.id)<>max(t.id) then cast(min(t.id) as varchar)+'-'+cast(max(t.id) as varchar) else cast(min(t.id) as varchar) end
from
(
select id,id-(select count(*) from test b where a.id>=b.id)  groupid
from test a
) t
group by groupid

强！
学到了。:)

注: 以下的为2005版本

1. 找到连续号码
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);

SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp     
              FROM dbo.T1) AS D
GROUP BY grp;
处理结果：
start_range    end_range
1                  3
100              101
103              106

2.找到缺失号码（用上述表演示）
（1）.生成缺号的值列表(引用函数dbo.fn_nums(@n AS BIGINT))
SELECT n FROM dbo.fn_nums(100000)
WHERE n BETWEEN (SELECT MIN(col1) FROM dbo.T1)
            AND (SELECT MAX(col1) FROM dbo.T1)
  AND NOT EXISTS(SELECT * FROM dbo.T1 WHERE col1 = n);
（2）.生成缺号的范围值
SELECT cur + 1 AS start_range, nxt - 1 AS end_range
FROM (SELECT col1 AS cur,
        (SELECT MIN(col1) FROM dbo.T1 AS B
        WHERE B.col1 > A.col1) AS nxt
      FROM dbo.T1 AS A) AS D
WHERE nxt - cur > 1;

注：表值函数dbo.fn_nums,生成整数序列
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0  AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1  AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2  AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3  AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4  AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5  AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n

--我也帖一个

create table tb (number varchar(6),日期 smalldatetime)
insert tb
select  '001', '2006-01-01'  union all
select  '001', '2006-01-02'  union all
select  '001', '2006-01-03'  union all
select  '001', '2006-01-04'  union all
select  '001', '2006-01-05'  union all
select  '001', '2006-01-07'  union all
select  '001', '2006-01-08'  union all
select  '003', '2006-01-09'  union all
select  '003', '2006-01-10'  union all
select  '003', '2006-01-11'
select number,min(日期) as 开始时间,max(日期) as 结束时间
from(
select a.number,a.日期,count(1) as ii
from tb a
join tb b on b.number=a.number and b.日期<=a.日期
group by a.number,a.日期
)c
group by number, DATEADD(day,-c.ii,日期)
go
drop table tb
/*
number 开始时间                        结束时间                                                 
------ ------------------------------- ----------------------------------
001    2006-01-01 00:00:00            2006-01-05 00:00:00
001    2006-01-07 00:00:00            2006-01-08 00:00:00
003    2006-01-09 00:00:00            2006-01-11 00:00:00
(3 row(s) affected)
*/

这是很典型的案例，我通常如下处理
select rtrim(a.id)+case when a.id=min(b.id) then '' else '-'+rtrim(min(b.id)) end
from test a inner join test b on a.id<=b.id
where not exists(select * from test where id=a.id-1) and not exists(select * from test where id=b.id+1)
group by a.id

这是很典型的案例，我通常如下处理
select rtrim(a.id)+case when a.id=min(b.id) then '' else '-'+rtrim(min(b.id)) end
from test a inner join test b on a.id<=b.id
where not exists(select * from test where id=a.id-1) and not exists(select * from test where id=b.id+1)
group by a.id

--我也貼個,借用臨時表更新
create table T(id int)
insert into T
              select 5
union all select 1
union all select 2
union all select 3
union all select 6
union all select 8
union all select 9
union all select 10
union all select 12
union all select 15
union all select 16
union all select 18
union all select 19


select id,tmp=0 into #t from T order by id

declare @id int,@tmp int
set @tmp=0
update #t
set @tmp=case when id=@id+1
                then @tmp
                else @tmp+1 end,
            @id=id,
            tmp=@tmp

select case when min(id)=max(id) then rtrim(min(id)) else rtrim(min(id))+'-'+rtrim(max(id)) end as [id]
from #t
group by tmp

/*
id                       
-------------------------
1-3
5-6
8-10
12
15-16
18-19
*/
drop table T,#t

/*=========================*/
--這種方法還可用來統計連續出現的某字符數字的個數
create table T(id int)
insert into T
              select 1
union all select 1
union all select 0
union all select 0
union all select 0
union all select 0
union all select 1
union all select 1
union all select 0
union all select 1
union all select 1
union all select 1
union all select 1

select id,tmp=0 into #t1 from T

declare @id1 int,@tmp1 int
set @tmp1=0
update #t1
set @tmp1=case when id=@id1
                then @tmp1
                else @tmp1+1 end,
            @id1=id,
            tmp=@tmp1

select id,count(*) as [count]
from #t1
group by id,tmp
order by tmp
/*
id          count     
----------- -----------
1          2
0          4
1          2
0          1
1          4
*/
drop table #t1,t

/*========================*/
--還可以用來合併字符串等等...

学习学习！！！