set nocount on
declare @temp table (k1 int identity,b varchar(10),c1 int)
insert into @temp values('x1',11)
insert into @temp values('x2',13)
insert into @temp values('x3',15)
insert into @temp values('x3',15)
insert into @temp values('x2',12)
insert into @temp values('x9',10)
insert into @temp values('x1',11)
insert into @temp values('x6',15)
insert into @temp values('x3',15)
insert into @temp values('x5',12
declare @max int
select @max=max(c1) from @temp
declare @temp_max table(starts int,ends int,diff int )
insert into @temp_max
select starts=v.k1,ends=isnull(min(case when v.k1<a.k1 and a.c1<>@max then a.k1 else null end )-1,max(case when v.k1<a.k1 then a.k1 else null end)),
isnull(min(case when v.k1<a.k1 and a.c1<>@max then a.k1 else null end )-1,max(case when v.k1<a.k1 then a.k1 else null end))-v.k1 as diff
from @temp v join @temp a on v.c1=@max
group by v.k1
having isnull(min(case when v.k1<a.k1 and a.c1<>@max then a.k1 else null end )-1,max(case when v.k1<a.k1 then a.k1 else null end))
>=v.k1 and
isnull(max(case when v.k1>a.k1 and a.c1<>@max then a.k1 else null end )+1,min(case when v.k1>a.k1 then a.k1 else null end))
=v.k1
select *
from @temp t
where exists (select * from @temp_max where diff=(select max(diff) from @temp_max) and (t.k1 between starts and ends))
如果在表中插入了连续的:
insert into tablename values(1,'x1',11)
insert into tablename values(2,'x2',15) --连续的15
insert into tablename values(3,'x3',15) --连续的15
insert into tablename values(4,'x3',14)
insert into tablename values(5,'x2',15)
insert into tablename values(6,'x4',13)
insert into tablename values(7,'x4',15) --连续的15
insert into tablename values(8,'x6',15) --连续的15
insert into tablename values(9,'x4',14)
那么你如果运行下面的原来sql的话,最后结果是:
a b c 连续的个数
----------- ---------- ----------- -----------
2 x2 15 2
3 x3 15 2
NULL NULL NULL 2
7 x4 15 2
8 x6 15 2
(所影响的行数为 5 行)
--原来的sql
create table tablename(a int ,b varchar(10),c int)
insert into tablename values(1,'x1',11)
insert into tablename values(2,'x2',15) --连续的15
insert into tablename values(3,'x3',15) --连续的15
insert into tablename values(4,'x3',14)
insert into tablename values(5,'x2',15)
insert into tablename values(6,'x4',13)
insert into tablename values(7,'x4',15) --连续的15
insert into tablename values(8,'x6',15) --连续的15
insert into tablename values(9,'x4',14)
declare @temp_max table(a int ,b varchar(10),c int)
declare @temp table(a int ,b varchar(10),c int)
declare @maxcount int
set @maxcount=0
declare @max_c int
select @max_c=max(c) from tablename
declare @recordcount int
set @recordcount=0
declare @a int
declare @b varchar(10)
declare @c int
declare max_cursor cursor for
select * from tablename
open max_cursor
fetch max_cursor into @a,@b,@c
while @@fetch_status =0
begin
if(@c=@max_c)
begin
insert into @temp values(@a,@b,@c)
set @recordcount=@recordcount+1
fetch max_cursor into @a,@b,@c
while @@fetch_status=0
begin
if(@c=@max_c)
begin
insert into @temp values(@a,@b,@c)
fetch max_cursor into @a,@b,@c
set @recordcount=@recordcount+1
end
else
begin
break
end
end
end
if(@maxcount=@recordcount and @maxcount<>0)
begin
if(@maxcount>=1)
insert into @temp_max values(null,null,null)
insert into @temp_max
select * from @temp
set @recordcount=0
delete from @temp
end
else
begin
if(@maxcount<@recordcount)
begin
delete from @temp_max
insert into @temp_max
select * from @temp
delete from @temp
set @maxcount=@recordcount
set @recordcount=0
end
else
set @recordcount=0
delete from @temp
end
fetch max_cursor into @a,@b,@c
[Quote=引用 12 楼 yygyogfny 的回复:]
/*
a b c
1 x1 11
2 x2 15
3 x3 15
4 x3 14
5 x2 15
6 x4 13
*/
create table #temp
(
a int identity(1,1),
b varchar(20),
c int
)
insert into #temp
select 'x1',11
union all
select 'x2',15
union all
select 'x3',15
union all
select 'x3',14
union all
select 'x2',15
union all
select 'x4',13
呵呵 结贴了呀!!!昨天没时间 今天弄了一下 感觉还是很麻烦 不过要比71楼的简单一些
create table #temp
(
a int identity(1,1),
b varchar(20),
c int
)
insert into #temp
select 'x1',11
union all
select 'x2',15
union all
select 'x3',15
union all
select 'x3',15
union all
select 'x2',13
union all
select 'x4',13
union all
select 'x4',15
union all
select 'x4',15
--第二种的解决方案
--定义临时表b
declare @b table
(
fid int, --起始位置的id
lid int --结束位置的id
)
declare @a int
declare @i int
declare @j int
declare @d int
set @j=0
--定义游标
declare colaa cursor
for
select a from #temp where c=(select max(c) from #temp) order by a
open colaa
fetch next from colaa into @a
while(@@FETCH_STATUS<>-1)
begin
IF(@@FETCH_STATUS<>-2)
begin
if(@j=0)--第一条
begin
insert into @b values(@a,0) --插入起始位置的id
print @a
set @j=1--标志位
end
else
begin
set @d=(select count(*)from @b)
if(@d<>0 and (@a-1)in(select a from #temp where c=(select max(c) from #temp)))--连续的情况,
begin
update @b set lid=@a where fid=(select top 1 fid from @b order by fid desc) --插入结束位置的id
print @a
end
else--下一下最大值的开始id
begin
insert into @b values(@a,0)--插入下一下最大值的开始id
print @a
end
end
end
fetch next from colaa into @a
end
--关闭游标
close colaa
deallocate colaa
select * from @b
select max(lid-fid)as 连续数 from @b
--查找最大集合
select * from #temp a, @b b where a.a>=b.fid and a.a<=b.lid and (b.lid-b.fid)=(select max(lid-fid)as 连续数 from @b)
SELECT * FROM BB WHERE (A001+1 IN(
SELECT A001 FROM BB WHERE A003=(select max(A003) AS A003 FROM BB) ) OR A001-1 IN(
SELECT A001 FROM BB WHERE A003=(select max(A003) AS A003 FROM BB) ) ) AND A003=(select max(A003) AS A003 FROM BB)
SELECT * FROM BB WHERE (A001+1 IN(
SELECT A001 FROM BB WHERE A003=(select max(A003) AS A003 FROM BB) ) OR A001-1 IN(
SELECT A001 FROM BB WHERE A003=(select max(A003) AS A003 FROM BB) ) ) AND A003=(select max(A003) AS A003 FROM BB)