想让他们变成区间形式 a b 001 004 007 009 如何才能办到,我需要的是一条sql语句 ------------------------------------ --构建数据 declare @tb table(num varchar(10)) insert into @tb select '001' insert into @tb select '002' insert into @tb select '003' insert into @tb select '004' insert into @tb select '007' insert into @tb select '008' insert into @tb select '009' --SQL 2000
--方法1
select a.num as [a],min(b.num) as [b] from (select * from @tb t where not exists(select 1 from @tb where num=t.num-1)) a, (select * from @tb t where not exists(select 1 from @tb where num=t.num+1)) b where a.num<=b.num group by a.num
--方法2
select a=min(num),b=max(num) from (select px=(select count(1) from @tb where num<=a.num),num from @tb a) b group by cast(num as int)-px
--SQL 2005
select min(num) as a,max(num) as b from ( select px = row_number() over(order by num),* from @tb )T group by cast(num as int)-px --第一种方法比较绕。 --后两种方法思路很简单,就是按cast(num as int)-px分组,然后取最小值和最大值。 --以上3种方法都能得到如下结果: /* a b ---------- ---------- 001 004 007 009