匹配查找某段时间内某字段相同的内容

--提问者:bmsword 明月剑

假设有以下结构的表A：
id    date          times
10    2009-01-01    3
11    2009-01-02    1
12    2009-01-03    3
13    2009-01-04    3
14    2009-01-05    2
15    2009-01-06    0
16    2009-01-07    0
17    2009-01-08    0
18    2009-01-09    0
19    2009-01-10    0
20    2009-01-05    6
21    2009-01-06    0
22    2009-01-07    0
23    2009-01-08    1
24    2009-01-09    3
25    2009-01-10    3
26    2009-01-09    0
27    2009-01-09    0
如何根据一个区间统计出跟它一样的区间?
比如以上的A表中,2009-01-02,2009-01-03,2009-01-04三天时间内,times分别为1,3,3,该如何写一条SQL语句或
存储过程找出2009-01-08 - 2009-01-10这个区间呢?因为他的times也分别为1,3,3.
当然了,数据量比较大的,希望高手帮写一个效率较高的存储过程或SQL语句.谢谢了.

--解答者:szx1999 蓉儿

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[date] datetime,[times] int)
insert [tb]
select 10,'2009-01-01',3 union all
select 11,'2009-01-02',1 union all
select 12,'2009-01-03',3 union all
select 13,'2009-01-04',3 union all
select 14,'2009-01-05',2 union all
select 15,'2009-01-06',0 union all
select 16,'2009-01-07',0 union all
select 17,'2009-01-08',1 union all
select 18,'2009-01-09',3 union all
select 19,'2009-01-10',3 union all
select 20,'2009-01-11',0 union all
select 21,'2009-01-12',0 union all
select 22,'2009-01-13',1 union all
select 23,'2009-01-14',3 union all
select 24,'2009-01-15',3 union all
select 25,'2009-01-16',1 union all
select 26,'2009-01-17',2 union all
select 27,'2009-01-18',3
go

--初始化
declare @b datetime,@e datetime,@d int,@bt int,@et int
select @b='2009-01-02',@e='2009-01-04',@d=datediff(d,@b,@e)
select @bt=times from tb where date=@b
select @et=times from tb where date=@e
--保存用于匹配的结果集
select rn=identity(int,0,1),* into # from tb where date between @b and @e
--求出匹配的所有集合中的第一条记录的id
select id into #1 from tb t
where times=@bt
and exists(select 1 from tb where times=@et and id=t.id+@d)
and not exists(select 1 from tb where date>t.date and date<t.date+@d and times<>(select times from # where rn=datediff(d,t.date,tb.date)))
--选出所有匹配的结果集
select * from tb a
join #1 b
on a.id between b.id and b.id+@d
/*
id          date                    times       id
----------- ----------------------- ----------- -----------
11          2009-01-02 00:00:00.000 1           11
12          2009-01-03 00:00:00.000 3           11
13          2009-01-04 00:00:00.000 3           11
17          2009-01-08 00:00:00.000 1           17
18          2009-01-09 00:00:00.000 3           17
19          2009-01-10 00:00:00.000 3           17
22          2009-01-13 00:00:00.000 1           22
23          2009-01-14 00:00:00.000 3           22
24          2009-01-15 00:00:00.000 3           22

(9 行受影响)
*/
drop table #
drop table #1