--So:
select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id+1)) a,
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id-1)) bwhere a.cnt+1=b.cnt
select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id+1)) a,
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id-1)) bwhere a.cnt+1=b.cnt
--查询处理
select id=cast(a.id as varchar)+'-'+cast(b.id as varchar)
from(
select sid=(select sum(1) from @a a1 where id<=a.id and not exists(select 1 from @a where id=a1.id+1))
,id=id
from @a a
where not exists(select 1 from @a where id=a.id+1)
)a join(
select sid=(select sum(1) from @a a1 where id<=a.id and not exists(select 1 from @a where id=a1.id-1))
,id=id
from @a a
where not exists(select 1 from @a where id=a.id-1)
) b on a.sid=b.sid-1
select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id+1)) a,
(select a.id,(select count(*) from @a b where b.id< a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id-1)) b
where a.cnt=b.cnt
Declare @a table(ID int)
Insert Into @a
Select 1 ID
Union
Select 2
Union
Select 3
Union
Select 7
Union
Select 9
Union
Select 10
Union
Select 18
Union
Select 19--查询处理
select id=cast(a.id as varchar)+'-'+cast(b.id as varchar)
from(
select sid=(select sum(1) from @a a1 where id<=a.id and not exists(select 1 from @a where id=a1.id+1))
,id=id
from @a a
where not exists(select 1 from @a where id=a.id+1)
)a join(
select sid=(select sum(1) from @a a1 where id<=a.id and not exists(select 1 from @a where id=a1.id-1))
,id=id
from @a a
where not exists(select 1 from @a where id=a.id-1)
) b on a.sid=b.sid-1/*--测试结果id
-------------------------------------------------------------
3-7
7-9
10-18(所影响的行数为 3 行)
--*/
select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from @a a ,(select a.id from @a a where not exists (select 1 from @a c where c.id=a.id-1)) b
where a.id<b.id
group by b.id
Insert Into @a
Select 1 ID
Union
Select 2
Union
Select 3
Union
Select 7
Union
Select 9
Union
Select 10
Union
Select 18
Union
Select 19select ID
,(select min(ID)
from @a
where ID>a.ID)
,(select min(ID)
from @a
where ID > a.ID) - ID
from @a a
where (select min(ID) from @a where ID > a.ID) - ID > 1
order by ID
select ID
,(select min(ID)
from #T
where ID>a.ID)
from #T a
where (select min(ID) from #T where ID > a.ID) - ID > 1
order by ID
create table t(id int)
insert t values(1)
insert t values(2)
insert t values(3)
insert t values(7)
insert t values(9)
insert t values(10)
insert t values(18)
insert t values(19)select convert(varchar,id)+'-'+convert(varchar,id2)
from
(select id,(select min(id) from t where id>a.id) as id2 from t a
where not exists(select 1 from t where id = a.id+1)
) a
where id2 is not null
-------------------------------------------------------------
3-7
7-9
10-18(所影响的行数为 3 行)
分两部分取出没有+1/-1的记录,记下位置 join
先 join 取最接近的记录 并差大于1
我的最短写法:
select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from @a a ,@a b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1
厉害!
select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from @a a ,@a b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1
能解释一下么?总觉得这方法是不是数据多了会有问题?
from
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id+1)) a,
(select a.id,(select count(*) from @a b where b.id<=a.id) cnt from @a a where not exists (select 1 from @a c where c.id=a.id-1)) bwhere a.cnt+1=b.cnt性能更好
Insert Into @a
Select 1 ID
Union
Select 2
Union
Select 3
Union
Select 7
Union
Select 9
Union
Select 10
Union
Select 18
Union
Select 19set nocount off select case when (select min(t2.id) from @a t2 where t2.id>t1.id)=t1.id+1--如果有下一个+1
then
case
when (select max(t3.id) from @a t3 where t3.id<t1.id)=t1.id-1 then null--如果有上一个-1
when (select max(t3.id) from @a t3 where t3.id<t1.id) is null then null
else convert(varchar,t1.id)+',' end
when (select max(t4.id) from @a t4 where t4.id>t1.id) is null then null
else
case when (select max(t3.id) from @a t3 where t3.id<t1.id)=t1.id-1 then convert(varchar,t1.id) --如果有上一个-1
when (select max(t3.id) from @a t3 where t3.id<t1.id) is null then null
else convert(varchar,t1.id) end
end as id into #table1
from @a t1 order by convert(int,t1.id)select replace(convert(varchar,t1.id)+'-'+convert(varchar,min(t2.id)),',','') as id2 from #table1 t1,#table1 t2
where t1.id<t2.id and t1.id not like '%,%'
group by t1.id order by convert(int,substring(t1.id,1,patindex('%-%',replace(convert(varchar,t1.id)+'-'+convert(varchar,min(t2.id)),',',''))-1))
drop table #table1
你的方法 好象是绕了一个大弯。。说说思路?
if 当前id=上一个id+1 then
if 当前id=下一id-1 then
else
end if
else
if 当前id=下一id-1 then
else
end if
end if哈哈,只是抛砖引玉,知道不行,不是要另外思路么?用case when ....
Three diferent formats
1-7
10-12
12-20
20-25
25-45
45-602
3
4
5
6
11
...2,3,4,5,6,11...
These scripts work off this tablecreate table #nos (i int)
insert #nos select 1
insert #nos select 7
insert #nos select 8
insert #nos select 9
insert #nos select 10
insert #nos select 12
insert #nos select 20
insert #nos select 25
insert #nos select 45
insert #nos select 60To give result in form
1-7
10-12
12-20
20-25
25-45
45-60select convert(varchar(10),imin.i) + ' - ' + convert(varchar(10),
(select min(i) from (select i = i from #nos where not exists (select * from #nos a2 where #nos.i-1 =
a2.i) and #nos.i <> (select min(i) from #nos)) as imax where imax.i > imin.i))
from
(select i = i from #nos where not exists (select * from #nos a2 where #nos.i+1 = a2.i) and #nos.i <>
(select max(i) from #nos)) as imin
To give result in form
2
3
4
5
6
11
...
in single statementselect ints.i
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) as ints left outer join #nos on ints.i = #nos.i
where #nos.i is null
and ints.i <= 60 and ints.i <> 0
order by ints.iin looptemp table
create table #a (i int)
declare @i int
select @i = 0
while @i < (select max(i) from #nos)
begin
set @i = @i + 1
if not exists (select * from #nos where i = @i)
insert #a select @i
end
select * from #a
To give results in form
2,3,4,5,6,11... declare @s varchar(1000)
select @s = coalesce(@s+',' + convert(varchar(10),i),convert(varchar(10),i)) from #a --from (select
top 100 percent i from #a order by i) as a
select @s
if object_id('tempdb..#t') is not null
drop table #t
select top 10000 id=identity(int,1,1) into #t from sysobjects a, sysobjects b
delete from #t where id not in (select top 2000 id from #t order by newid())select convert(varchar,getdate(),121)+'Noindex 1 begin'select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from #t b where b.id<=a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id+1)) a,
(select a.id,(select count(*) from #t b where b.id< a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id-1)) b
where a.cnt=b.cnt
order by a.idselect convert(varchar,getdate(),121)+'Noindex 1 end/2Begin'select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from #t a ,#t b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1select convert(varchar,getdate(),121)+'Noindex 2 End'create UNIQUE CLUSTERED index xx on #t (id)
select convert(varchar,getdate(),121)++'Index 1 begin'select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from #t b where b.id<=a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id+1)) a,
(select a.id,(select count(*) from #t b where b.id< a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id-1)) b
where a.cnt=b.cnt
order by a.id
select convert(varchar,getdate(),121)+'Index 1 End/2 Begin'
select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from #t a ,#t b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1
select convert(varchar,getdate(),121) +'Index 2 End'
if object_id('tempdb..#t') is not null
drop table #t
select top 10000 id=identity(int,1,1) into #t from sysobjects a, sysobjects b
delete from #t where id not in (select top 2000 id from #t order by newid())select convert(varchar,getdate(),121)+'Noindex 1 begin'select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from #t b where b.id<=a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id+1)) a,
(select a.id,(select count(*) from #t b where b.id< a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id-1)) b
where a.cnt=b.cnt
order by a.idselect convert(varchar,getdate(),121)+'Noindex 1 end/2Begin'select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from #t a ,#t b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1select convert(varchar,getdate(),121)+'Noindex 2 End/3Begin'
select convert(varchar,id)+'-'+convert(varchar,id2)
from
(select id,(select min(id) from #t where id>a.id) as id2 from #t a
where not exists(select 1 from t where id = a.id+1)
) a
where id2 is not null
select convert(varchar,getdate(),121)+'Noindex 3 End'2004-01-08 16:03:46.687Noindex 1 begin
2004-01-08 16:03:54.727Noindex 1 end/2Begin
2004-01-08 16:03:59.873Noindex 2 End/3Begin
2004-01-08 16:04:02.617Noindex 3 End第三种更快一点
create UNIQUE CLUSTERED index xx on #t (id)
select convert(varchar,getdate(),121)++'Index 1 begin'select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from #t b where b.id<=a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id+1)) a,
(select a.id,(select count(*) from #t b where b.id< a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id-1)) b
where a.cnt=b.cnt
order by a.id
select convert(varchar,getdate(),121)+'Index 1 End/2 Begin'
select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from #t a ,#t b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1
select convert(varchar,getdate(),121) +'Index 2 End/3 Begin'select convert(varchar,id)+'-'+convert(varchar,id2)
from
(select id,(select min(id) from #t where id>a.id) as id2 from #t a
where not exists(select 1 from t where id = a.id+1)
) a
where id2 is not null
select convert(varchar,getdate(),121)+'Index 3 End'
2004-01-08 16:10:19.320Index 1 begin
2004-01-08 16:10:23.203Index 1 End/2 Begin
2004-01-08 16:10:26.347Index 2 End/3 Begin
2004-01-08 16:10:26.820Index 3 End第三个优势更加明显。
2004-01-08 16:27:07.047 Noindex 1 begin2004-01-08 16:27:08.600 Noindex 1 end/2Begin2004-01-08 16:27:11.393NoIndex 2 End/3 Begin2004-01-08 16:27:13.587NoIndex 3 End2004-01-08 16:27:13.617 Index 1 begin2004-01-08 16:27:15.190 Index 1 End/2 Begin2004-01-08 16:27:17.743Index 2 End/3 Begin2004-01-08 16:27:17.863Index 3 End晕了,醒的时候再试。。
from @a a left join @a b on a.id<b.id
group by b.id
having max(a.id)<>b.id-1
类似realgz(realgz)
drop table #t
select top 10000 id=identity(int,1,1) into #t from sysobjects a, sysobjects b
delete from #t where id not in (select top 2000 id from #t order by newid())select convert(varchar,getdate(),121)+'Noindex 1 begin'select convert(varchar,a.id)+'-'+convert(varchar,b.id)
from
(select a.id,(select count(*) from #t b where b.id<=a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id+1)) a,
(select a.id,(select count(*) from #t b where b.id< a.id) cnt from #t a where not exists (select 1 from #t c where c.id=a.id-1)) b
where a.cnt=b.cnt
order by a.idselect convert(varchar,getdate(),121)+'Noindex 1 end/2Begin'select convert(varchar,max(a.id))+'-'+convert(varchar,b.id)
from #t a ,#t b
where a.id<b.id
group by b.id
having b.id-max(a.id)>1select convert(varchar,getdate(),121)+'Noindex 2 End/3Begin'
select convert(varchar,id)+'-'+convert(varchar,id2)
from
(select id,(select min(id) from #t where id>a.id) as id2 from #t a
where not exists(select 1 from #t where id = a.id+1)
) a
where id2 is not nullselect convert(varchar,getdate(),121)+'Noindex 3 End/4Begin'
create table #tmp (id int, no int identity(1,1))
insert into #tmp(id) select id from #t
select cast(a.id as varchar) + '-' + cast(b.id as varchar) from #tmp a left outer join #tmp b on a.no + 1 = b.no where a.id + 1 <> b.iddrop table #tmpselect convert(varchar,getdate(),121)+'Noindex 4 End'
2004-01-09 01:28:23.193Noindex 1 begin
2004-01-09 01:28:27.350Noindex 1 end/2Begin
2004-01-09 01:28:30.483Noindex 2 End/3Begin
2004-01-09 01:28:32.587Noindex 3 End/4Begin
2004-01-09 01:28:33.247Noindex 4 End