请高手帮写一条SQL语句或存储过程.

[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语句.谢谢了.

[xxxooo7]

谢谢楼主~

[springwit]

路过

[crazyxf]

[Quote=引用 67 楼 toniz 的回复:]
你没弄清我给的思路吧 你跑一下数据吧 这个逻辑区间重叠的也是取得出来的吧

select a.id,b.id,c.id from
(select ID,DT,TM from xwjtest1 where tm =1) a,
(select ID,DT,TM from xwjtest1 where tm =3) b,
(select ID,DT,TM from xwjtest1 where tm =3) c
where a.id =b.id -1 and a.id=c.id-2

思路是把区间的值符合要求的组合全部弄出来，然后过滤剩下 ID连续的值。所以你说的交叉什么的也能取得出来的。
…
[/Quote]
哥们你的方案就别说了，改变一下规律就得重写代码，你觉得很有意思吗？

[yaozqwise]

学习

[crazyxf]

擦，来晚了，那就学习下吧，不动脑子了，嘿嘿

[verry1230]

高手。。。

[toniz]

如果你要查的区域不是很大的话，可以考虑简单点的方法，如下：
select a.id,b.id,c.id from
(select ID,DT,TM from xwjtest1 where tm =1) a,
(select ID,DT,TM from xwjtest1 where tm =3) b,
(select ID,DT,TM from xwjtest1 where tm =3) c
where a.id =b.id -1 and a.id=c.id-2


ID ID ID
1 11 12 13
2 17 18 19
3 22 23 24

[kkxl8]

学习中

[crazyxf]

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',1 union all
select 14,'2009-01-05',3 union all
select 15,'2009-01-06',0 union all
select 16,'2009-01-07',3 union all
select 17,'2009-01-08',1 union all
select 18,'2009-01-09',3 union all
select 19,'2009-01-10',1 union all
select 20,'2009-01-11',3 union all
select 21,'2009-01-12',1 union all
select 22,'2009-01-13',3 union all
select 23,'2009-01-14',0 union all
select 24,'2009-01-15',1 union all
select 25,'2009-01-16',3 union all
select 26,'2009-01-17',1 union all
select 27,'2009-01-18',3
go

重叠区间没有考虑，在上个匹配区间内重复匹配了，如果 日期开始是 2009-1-2 结束日期 2009-1-5 ，上面的方法就会产生日期重叠。

[toniz]

楼主好像说了不会重复 呵呵
声明一下,日期不会重复的,上边给出的是没有处理过的,上边只是举个例子.

[xiaodage]

看看 但不明白

[lclichao2006]

好深奥哦，顶一下

[故剑情深]

不太会！顶一下！

[itcrazyman]

没明白意思,顶

[toniz]

[Quote=引用 70 楼 crazyxf 的回复:]
引用 67 楼 toniz 的回复:
你没弄清我给的思路吧 你跑一下数据吧 这个逻辑区间重叠的也是取得出来的吧

select a.id,b.id,c.id from
(select ID,DT,TM from xwjtest1 where tm =1) a,
(select ID,DT,TM from xwjtest1 where tm =3) b,
(select ID,DT,TM from xwjtest1 where tm =3) c
where a.id =b.id -1 and a.id=c.id-2

思路是把区间的值符合要求的组合全部弄出来，然后过滤剩下 ID连续的值。所以你说的交叉…
[/Quote]

莫装B 我说了只是提供一种思路。
在最最前面也说了，如果区间不是很复杂的话可以考虑。

[crazyxf]

[Quote=引用 76 楼 toniz 的回复:]
引用 70 楼 crazyxf 的回复:
引用 67 楼 toniz 的回复:
你没弄清我给的思路吧 你跑一下数据吧 这个逻辑区间重叠的也是取得出来的吧

select a.id,b.id,c.id from
(select ID,DT,TM from xwjtest1 where tm =1) a,
(select ID,DT,TM from xwjtest1 where tm =3) b,
(select ID,DT,TM from xwjtest1 where tm =3) c
where a.id =b.id -1 and a.id=c.id-2

思路是把区间的值符合要求的组合全部弄出来，然后过滤剩…
[/Quote]
什么叫解决方案，就是要有一定通用性，修改唯一入口参数就可以适应改变，这是程序设计的基本原理，如果你编程序也这个习惯，你会害人害己的。

[yanyuchonglou]

我想看看叶子给的想法。

[sdfgdfgsdfgsfg]

declare @tb table([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-12',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

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
--保存用于匹配的结果集(id连续时间连续)
declare @temp table(rn int identity(1,1),[id] int,[date] datetime,[times] int)
insert @temp select * from @tb where date between @b and @e
--求出匹配的所有集合中的第一条记录的id
declare @tempId table(id int)
insert into @tempId select id from @tb t
where times=@bt
and exists
(
select 1 from @tb where date = DATEADD(DAY,1,t.date)
and times =(select times from @temp where rn=2)
)
and exists
(
select 1 from @tb where date = DATEADD(DAY,2,t.date)
and times =(select times from @temp where rn=3)
)
select * from @tb a
join @tempId b
on a.id between b.id and b.id+@d

[shinger126]

declare @t table(id int,date datetime,times int)
insert @t select 10,'2009-01-01',3
insert @t select 11,'2009-01-02',1
insert @t select 12,'2009-01-03',3
insert @t select 13,'2009-01-04',3
insert @t select 14,'2009-01-05',2
insert @t select 15,'2009-01-06',0
insert @t select 16,'2009-01-07',0
insert @t select 17,'2009-01-08',1
insert @t select 18,'2009-01-09',0
insert @t select 19,'2009-01-10',0
insert @t select 20,'2009-01-11',2
insert @t select 21,'2009-01-12',0
insert @t select 22,'2009-01-13',0
insert @t select 23,'2009-01-14',1
insert @t select 24,'2009-01-15',3
insert @t select 25,'2009-01-16',3
insert @t select 26,'2009-01-18',0
insert @t select 27,'2009-01-19',0

create proc [dbo].[sp_find] (@date_b date,@date_e date) as

declare @days int
set @days = DATEDIFF(day,@date_b,@date_e)+1

declare @t table(id int,[date] date,times int,xh int identity(1,1) )

declare @date date,@date_s date

select @date = MIN(date) from A

declare @times1 int,@times2 int

declare @i int,@date1 date,@k int

while @date<=(select DATEADD(day,@days,max(date)) from A)
begin

set @i = 1
set @k = 1
set @date1 = @date
set @date_s = @date_b
while @i < = @days
begin
select @times1 = times from A where [date] = @date_s
select @times2 = times from A where [date] = @date1
if @times1 <> @times2
begin
set @k = 0
end
set @i = @i + 1
set @date1 = DATEADD(day,1,@date1)
set @date_s = DATEADD(day,1,@date_s)
end
print '@k=' + cast(@k as varchar(10))
if @k = 1
begin

insert into @t (id,[date],times)
select ID,[date],times from A
where [date] <= dateadd(day,-1,@date1) and [date] >= DATEADD(day,-@days,@date1)
end
set @date = DATEADD(day,1,@date)

end

select id,[date],times from @t order by xh

执行exec sp_find '2009-01-02','2009-01-04'
结果
id date times
11 2009-01-02 1
12 2009-01-03 3
13 2009-01-04 3
23 2009-01-14 1
24 2009-01-15 3
25 2009-01-16 3

过程在SQL2008里面才可以，因为date数据类型是2008新添加的，如果要在其他版本建立过程，要把date数据类型定义为varchar类型。

[shinger126]

上面这个过程的思路是用嵌套循环加上变量来判断，外循环日期，内循环查询的天数，判断变量在内循环开始前初始化为1，如果内循环到某条记录不匹配，判断变量变为1，在内循环结束后判断变量，如果为1就插入结果集。这个办法虽然笨了点，但是结果准确，而且不管查询的日期天数相差多少都可以准确地得出结果。