/******************************************************************************/
/*回复：20080521003总：00039 */
/*主题：连续数据相同 */
/*作者：二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @checktime1 table([d] datetime)
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select null
insert into @checktime1 select '1899-12-30 08:00:00.000'
insert into @checktime1 select '1899-12-30 07:54:00.000'
insert into @checktime1 select null
insert into @checktime1 select '1900-01-01 07:58:00.000'
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select '1900-01-01 07:58:00.000'
insert into @checktime1 select null
insert into @checktime1 select '1900-01-01 07:56:00.000'
insert into @checktime1 select '1900-01-01 07:59:00.000'
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select null
insert into @checktime1 select '1900-01-01 07:52:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'
insert into @checktime1 select '1900-01-01 07:54:00.000'--代码--------------------------------------------------------------------------
select * from @checktime1 where d in (select d from @checktime1 group by d having count(*) >=7)
go/*结果--------------------------------------------------------------------------
d
------------------------------------------------------
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
--清除------------------------------------------------------------------------*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (checktime1 datetime)
insert into #T
select '1900-01-01 07:59:00.000' union all
select null union all
select null union all
select null union all
select null union all
select null union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select '1900-01-01 07:59:00.000' union all
select null union all
select '1899-12-30 08:00:00.000' union all
select '1899-12-30 07:54:00.000' union all
select null union all
select '1900-01-01 07:58:00.000' union all
select null union all
select null union all
select '1900-01-01 07:58:00.000' union all
select null union all
select '1900-01-01 07:56:00.000' union all
select '1900-01-01 07:59:00.000' union all
select null union all
select null union all
select null union all
select null union all
select '1900-01-01 07:52:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000' union all
select '1900-01-01 07:54:00.000'
goalter table #T add id int
godeclare @id int,@check datetime
set @id=0
update #T set id=@id, @id=case when checktime1=@check then @id else @id+1 end, @check=checktime1select checktime1 from #T as t where (select count(1) from #T where id=t.id)>=7
/*
checktime1
-----------------------
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:59:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
1900-01-01 07:54:00.000
*/