sql 最快筛出重复数据！

[xikboy]

有一表：

id rq sktime
1 01 '08:10'
1 02 '08:11'
1 03 '08:10'
.....


有一个月的数据，现在要检测出一个月内的数据sktime 连续不能出现三笔重复，如果出现，中间那笔时间重新生成

[永生天地]

--如何求连续的个数

create table tb (col1 int,col2 int);
insert into tb
select 1, 1
union select 2 ,9
union select 3, 9
union select 4, 2
union select 5, 9
union select 6, 9
union select 7, 9
union select 8, 9
union select 9, 9
union select 10, 9
union select 11, 1
union select 12, 9
union select 13, 9
union select 14, 9
union select 15, 9
union select 16, 9


with t as
(select  t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn  from tb t1),
t1 as (
select max(t.col1) as id, t.col2,COUNT(1) CNT
    from t  
    group by  t.col2,t.col1-rn

)
select * from t1 where cnt>=5


id          col2        CNT         
----------- ----------- ----------- 
10          9           6
16          9           5

（所影响的行数为 2 行）



select max(a.col1) col1 ,COL2,count(1) as [count]
from 
(
select L.*,
ROW_NUMBER() OVER ( PARTITION BY  COL2  ORDER BY  COL1 DESC ) + COL1 AS GGM 
FROM tb L
) A
group by A.COL2,GGM
having count(1)>=5
order by min(A.COL1)

col1        COL2        count       
----------- ----------- ----------- 
10          9           6
16          9           5

（所影响的行数为 2 行）

[xikboy]

sql 2000

[永生天地]

if object_id('tb')is not null drop table tb
go
create table tb(id int,rq varchar(2),sktime varchar(5))
go
insert tb 
select 1, '01', '08:10' union all
select 1, '02', '08:11' union all
select 1, '03', '08:10' union all
select 1, '04', '08:10' union all
select 1, '05', '08:20' union all
select 1, '06', '08:20' union all
select 1, '07', '08:20' union all
select 1, '08', '08:30' 


select A.id,min(A.rq) rq ,A.sktime,count(1) as [count]
from 
(
select L.id,L.rq,L.sktime,
rowid=count(*)-L.rq
FROM tb L left join tb M on L.sktime=M.sktime and L.rq>=M.rq 
group by L.id,L.rq,L.sktime
) A
group by A.id,A.sktime,A.rowid
having count(1)>=3
order by min(A.rq)

/*id          rq   sktime count       
----------- ---- ------ ----------- 
1           05   08:20  3

（所影响的行数为 1 行）
*/

[songgang622]

看看，学习一下。

[xikboy]

强。

更正一下：

select A.id,min(A.rq) rq ,A.sktime,count(1) as [count]
from
(
select L.id,L.rq,L.sktime,
rowid=count(*)-L.rq
FROM tb L left join tb M on L.sktime=M.sktime and L.rq>=M.rq and l.id=m.id
group by L.id,L.rq,L.sktime
) A
group by A.id,A.sktime,A.rowid
having count(1)>=3
order by min(A.rq)

[xikboy]

不过不理解意思，能否解释一下不？ 那就更感谢了！