假设有以下结构的表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语句.谢谢了.
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语句.谢谢了.
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
declare @begdate datetime,@enddate datetime
select @begdate='2009-01-02',
@enddate='2009-01-04'
select *
from (
select *
from @t a
where not exists(select 1 from @t where id=a.id and id not in(select id from @t where date between @begdate and @enddate))
) t
where exists(select 1 from @t where id=t.id and date between @begdate and @enddate) id date times
----------- ----------------------- -----------
11 2009-01-02 00:00:00.000 1
12 2009-01-03 00:00:00.000 3
13 2009-01-04 00:00:00.000 3(3 行受影响)
if object_id ('A') is not null
drop table A
if OBJECT_ID('pro_c') is not null
drop procedure pro_c
go
create table A (id int,[date] datetime ,times int)
insert into A 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',0
union all select 18,'2009-01-09',0
union all select 19,'2009-01-10',0
union all select 20,'2009-01-05',6
union all select 21,'2009-01-06',0
union all select 22,'2009-01-07',0
union all select 23,'2009-01-08',1
union all select 24,'2009-01-09',3
union all select 25,'2009-01-10',3
union all select 26,'2009-01-09',0
union all select 27,'2009-01-09',0
go
create procedure pro_c (@da1 datetime,@da2 datetime)
as
set nocount on
select * from A where times in
(select times from A WHERE [DATE] in ('2009-01-02','2009-01-03','2009-01-04'))
and [date] between @da1 and @da2
set nocount off
goexec pro_c '2009-01-08','2009-01-10'
(18 行受影响)
id date times
----------- ----------------------- -----------
23 2009-01-08 00:00:00.000 1
24 2009-01-09 00:00:00.000 3
25 2009-01-10 00:00:00.000 3
if object_id ('A') is not null
drop table A
if OBJECT_ID('pro_c') is not null
drop procedure pro_c
go
create table A (id int,[date] datetime ,times int)
insert into A 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',0
union all select 18,'2009-01-09',0
union all select 19,'2009-01-10',0
union all select 20,'2009-01-05',6
union all select 21,'2009-01-06',0
union all select 22,'2009-01-07',0
union all select 23,'2009-01-08',1
union all select 24,'2009-01-09',3
union all select 25,'2009-01-10',3
union all select 26,'2009-01-09',0
union all select 27,'2009-01-09',0
go
create procedure pro_c (@da1 datetime,@da2 datetime)
as
set nocount on
select * from A where times in
(select times from A WHERE [DATE] in ('2009-01-02','2009-01-03','2009-01-04'))
and [date] between @da1 and @da2
set nocount off
goexec pro_c '2009-01-08','2009-01-10'
(18 行受影响)
id date times
----------- ----------------------- -----------
23 2009-01-08 00:00:00.000 1
24 2009-01-09 00:00:00.000 3
25 2009-01-10 00:00:00.000 3
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
declare @begdate datetime,@enddate datetime
select @begdate='2009-01-05',
@enddate='2009-01-08'
select *
from (
select *
from @t a
where not exists(select 1 from @t where id=a.id and id not in(select id from @t where date between @begdate and @enddate))
) t
where exists(select 1 from @t where id=t.id and date between @begdate and @enddate) id date times
----------- ----------------------- -----------
14 2009-01-05 00:00:00.000 2
15 2009-01-06 00:00:00.000 0
16 2009-01-07 00:00:00.000 0
17 2009-01-08 00:00:00.000 1(4 行受影响)
(select times from A WHERE [DATE] in ('2009-01-02','2009-01-03','2009-01-04'))
and [date] between '2009-01-01' and '2009-01-21'----------- ------------------------------------------------------ ----------- -----------
10 2009-01-01 00:00:00.000 3 2
11 2009-01-02 00:00:00.000 1 3
12 2009-01-03 00:00:00.000 3 2
13 2009-01-04 00:00:00.000 3 1
23 2009-01-08 00:00:00.000 1 3
24 2009-01-09 00:00:00.000 3 2
25 2009-01-10 00:00:00.000 3 1(所影响的行数为 7 行)
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
declare @begdate datetime,@enddate datetime
select @begdate='2009-01-05',
@enddate='2009-01-08'select *
from (
select *
from @t a
where not exists(select 1 from @t where id=a.id and id in(select id from @t where date between @begdate and @enddate))
) t
where exists(
select *
from @t b
where exists(select 1 from @t where id=b.id and times in(select times from @t where date between @begdate and @enddate))
and b.id=t.id)id date times
----------- ----------------------- -----------
11 2009-01-02 00:00:00.000 1
18 2009-01-09 00:00:00.000 0
19 2009-01-10 00:00:00.000 0
20 2009-01-11 00:00:00.000 2
21 2009-01-12 00:00:00.000 0
22 2009-01-13 00:00:00.000 0
23 2009-01-14 00:00:00.000 1
26 2009-01-18 00:00:00.000 0
27 2009-01-19 00:00:00.000 0(9 行受影响)
insert tb select 10,'2009-01-01',3
insert tb select 11,'2009-01-02',1
insert tb select 12,'2009-01-03',3
insert tb select 13,'2009-01-04',3
insert tb select 14,'2009-01-05',2
insert tb select 15,'2009-01-06',0
insert tb select 16,'2009-01-07',0
insert tb select 17,'2009-01-08',1
insert tb select 18,'2009-01-09',0
insert tb select 19,'2009-01-10',0
insert tb select 20,'2009-01-11',2
insert tb select 21,'2009-01-12',0
insert tb select 22,'2009-01-13',0
insert tb select 23,'2009-01-14',1
insert tb select 24,'2009-01-15',3
insert tb select 25,'2009-01-16',3
insert tb select 26,'2009-01-18',0
insert tb select 27,'2009-01-19',0
go
--建立一个临时表
create table tb2 (id int,date datetime,times int)
insert tb2 select 1,'2009-01-02',1
insert tb2 select 2,'2009-01-03',3
insert tb2 select 3,'2009-01-04',3
goselect m.* from tb m,
(
select t1.id id1, t2.id id2, t3.id id3 from
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 1) t1,
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 2) t2,
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 3) t3
where datediff(day,t1.date,t2.date) = 1 and datediff(day,t2.date,t3.date) = 1
) n
where m.id = n.id1 or m.id = n.id2 or m.id = n.id3
order by iddrop table tb,tb2/*
id date times
----------- ------------------------------------------------------ -----------
11 2009-01-02 00:00:00.000 1
12 2009-01-03 00:00:00.000 3
13 2009-01-04 00:00:00.000 3
23 2009-01-14 00:00:00.000 1
24 2009-01-15 00:00:00.000 3
25 2009-01-16 00:00:00.000 3(所影响的行数为 6 行)
*/
create table tb (id int,date datetime,times int)
insert tb select 10,'2009-01-01',3
insert tb select 11,'2009-01-02',1
insert tb select 12,'2009-01-03',3
insert tb select 13,'2009-01-04',3
insert tb select 14,'2009-01-05',2
insert tb select 15,'2009-01-06',0
insert tb select 16,'2009-01-07',0
insert tb select 17,'2009-01-08',1
insert tb select 18,'2009-01-09',0
insert tb select 19,'2009-01-10',0
insert tb select 20,'2009-01-11',2
insert tb select 21,'2009-01-12',0
insert tb select 22,'2009-01-13',0
insert tb select 23,'2009-01-14',1
insert tb select 24,'2009-01-15',3
insert tb select 25,'2009-01-16',3
insert tb select 26,'2009-01-18',0
insert tb select 27,'2009-01-19',0
go
declare @begdate datetime,@enddate datetime
select @begdate='2009-01-05',
@enddate='2009-01-08'
--建立一个临时表
select id=identity(int,1,1),date,times into tb2
from tb where date between @begdate and @enddate
go
--开始查询
select m.* from tb m,
(
select t1.id id1, t2.id id2, t3.id id3 ,t4.id id4 from
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 1) t1,
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 2) t2,
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 3) t3,
(select t.* from tb t, tb2 n where t.times = n.times and n.id = 4) t4
where datediff(day,t1.date,t2.date) = 1 and datediff(day,t2.date,t3.date) = 1 and datediff(day,t3.date,t4.date) = 1
) n
where m.id = n.id1 or m.id = n.id2 or m.id = n.id3 or m.id=n.id4
order by iddrop table tb,tb2
id date times
----------- ----------------------- -----------
14 2009-01-05 00:00:00.000 2
15 2009-01-06 00:00:00.000 0
16 2009-01-07 00:00:00.000 0
17 2009-01-08 00:00:00.000 1
20 2009-01-11 00:00:00.000 2
21 2009-01-12 00:00:00.000 0
22 2009-01-13 00:00:00.000 0
23 2009-01-14 00:00:00.000 1(8 行受影响)
需要改了代码后才能查出来
SQL直接查恐怕不容易,但是字符串匹配效率又太低..
等待高手...
@begdate='2009-01-04'
@enddate='2009-01-05'
-->>是什么意思呢?就8楼的结果,是从上面选出随便一个子集还是区间是固定的?
假设有以下结构的表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 1
18 2009-01-09 3
19 2009-01-10 3
20 2009-01-11 0
21 2009-01-12 0
22 2009-01-13 1
23 2009-01-14 3
24 2009-01-15 3现在假设我已经知道了2009-01-02~2009-01-04这连续的3天中,times的值分别为1,3,3. 现在要在整个表中找出所有连续区间(日期)中times的值完全与之匹配的区间,比如上边的2009-01-08~2009-01-10, 2009-01-13~2009-01-15两个区间,就是符合条件的,因为在这个区间中times的值也是1,3,3.
非常感谢各位朋友的踊跃参与,目前为止,还没有完全符合要求的解决方案.继续等待高手出现.
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
join #1 b
on a.id between b.id and b.id+@d
/*
id date times
----------- ----------------------- -----------
11 2009-01-02 00:00:00.000 1
12 2009-01-03 00:00:00.000 3
13 2009-01-04 00:00:00.000 3
17 2009-01-08 00:00:00.000 1
18 2009-01-09 00:00:00.000 3
19 2009-01-10 00:00:00.000 3
22 2009-01-13 00:00:00.000 1
23 2009-01-14 00:00:00.000 3
24 2009-01-15 00:00:00.000 3(9 行受影响)
*/
也非常感谢积极参与的各位.
10 2009-01-01 3
11 2009-01-02 1
12 2009-01-03 3
13 2009-01-04 3
select id,data,issame,count() over (partition by issame) from (
select id,data,to_char(times)||to_char(nextday)||to_char(next2day) issame from (
select id,data,times,nextday,lead(nextday) over (order by times) next2day from (
select id,data,times,lead(times) over(order by times)nextday from table
)))select id,data,times,lead(times) over(order by times)nextday from table 这里取得下一天的次数,
select id,data,times,nextday,lead(nextday) over (order by times) next2day from ( 这里取得下峡谷天的次数,
select id,data,to_char(times)||to_char(nextday)||to_char(next2day) issame from (这里是把三个次数合并,
select id,data,issame,count() over (partition by issame) from (这里用分组的方式去判断这边的有多少个
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
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 ,上面的方法就会产生日期重叠。
(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不连续,可以用日期相减。这里只提供个思路,就不那么麻烦了。
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',3declare @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
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) asdeclare @days int
set @days = DATEDIFF(day,@date_b,@date_e)+1declare @t table(id int,[date] date,times int,xh int identity(1,1) )declare @date date,@date_s dateselect @date = MIN(date) from Adeclare @times1 int,@times2 intdeclare @i int,@date1 date,@k intwhile @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)
endselect 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类型。