请高手帮写一条SQL语句或存储过程.
假设有以下结构的表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语句.谢谢了.
[解决办法]
没明白你说啥
[解决办法]
你那些日期很多重复的，这个才麻烦。
[解决办法]
declare @t table(id int,date datetime,times int)insert @t select 10,'2009-01-01',3insert @t select 11,'2009-01-02',1insert @t select 12,'2009-01-03',3insert @t select 13,'2009-01-04',3insert @t select 14,'2009-01-05',2insert @t select 15,'2009-01-06',0insert @t select 16,'2009-01-07',0insert @t select 17,'2009-01-08',1insert @t select 18,'2009-01-09',0insert @t select 19,'2009-01-10',0insert @t select 20,'2009-01-11',2insert @t select 21,'2009-01-12',0insert @t select 22,'2009-01-13',0insert @t select 23,'2009-01-14',1insert @t select 24,'2009-01-15',3insert @t select 25,'2009-01-16',3insert @t select 26,'2009-01-18',0insert @t select 27,'2009-01-19',0declare @begdate datetime,@enddate datetimeselect @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)) ) twhere 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 112 2009-01-03 00:00:00.000 313 2009-01-04 00:00:00.000 3(3 行受影响)
[解决办法]
if object_id ('A') is not null drop table Aif OBJECT_ID('pro_c') is not null drop procedure pro_cgocreate 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',0gocreate 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 offgoexec pro_c '2009-01-08','2009-01-10'(18 行受影响)id date times----------- ----------------------- -----------23 2009-01-08 00:00:00.000 124 2009-01-09 00:00:00.000 325 2009-01-10 00:00:00.000 3
[解决办法]
学习..
[解决办法]
这个好难呀 记号一下。
要查找的区间是固定的吗
[解决办法]
--用的九楼的数据,把需要的条件放入一个临时表.速度不知道怎么样?试试吧.
create table tb (id int,date datetime,times int)insert tb select 10,'2009-01-01',3insert tb select 11,'2009-01-02',1insert tb select 12,'2009-01-03',3insert tb select 13,'2009-01-04',3insert tb select 14,'2009-01-05',2insert tb select 15,'2009-01-06',0insert tb select 16,'2009-01-07',0insert tb select 17,'2009-01-08',1insert tb select 18,'2009-01-09',0insert tb select 19,'2009-01-10',0insert tb select 20,'2009-01-11',2insert tb select 21,'2009-01-12',0insert tb select 22,'2009-01-13',0insert tb select 23,'2009-01-14',1insert tb select 24,'2009-01-15',3insert tb select 25,'2009-01-16',3insert tb select 26,'2009-01-18',0insert tb select 27,'2009-01-19',0go--建立一个临时表create table tb2 (id int,date datetime,times int)insert tb2 select 1,'2009-01-02',1insert tb2 select 2,'2009-01-03',3insert tb2 select 3,'2009-01-04',3goselect 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) t3where datediff(day,t1.date,t2.date) = 1 and datediff(day,t2.date,t3.date) = 1) nwhere m.id = n.id1 or m.id = n.id2 or m.id = n.id3order by iddrop table tb,tb2/*id date times ----------- ------------------------------------------------------ ----------- 11 2009-01-02 00:00:00.000 112 2009-01-03 00:00:00.000 313 2009-01-04 00:00:00.000 323 2009-01-14 00:00:00.000 124 2009-01-15 00:00:00.000 325 2009-01-16 00:00:00.000 3（所影响的行数为 6 行）*/
[解决办法]
如果条件不定,不好搞.
[解决办法]
好像现在还没有人完全实现这个功能哦，看来要用字符串匹配了。SQL直接查恐怕不容易，但是字符串匹配效率又太低..等待高手...
[解决办法]
选出完全匹配一个区间的
-->>是什么意思呢？就8楼的结果，是从上面选出随便一个子集还是区间是固定的？
[解决办法]
用游标或循环判断吧。
[解决办法]
偶顶
[解决办法]
用游标或循环判断吧。
[解决办法]
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[date] datetime,[times] int)insert [tb]select 10,'2009-01-01',3 union allselect 11,'2009-01-02',1 union allselect 12,'2009-01-03',3 union allselect 13,'2009-01-04',3 union allselect 14,'2009-01-05',2 union allselect 15,'2009-01-06',0 union allselect 16,'2009-01-07',0 union allselect 17,'2009-01-08',1 union allselect 18,'2009-01-09',3 union allselect 19,'2009-01-10',3 union allselect 20,'2009-01-11',0 union allselect 21,'2009-01-12',0 union allselect 22,'2009-01-13',1 union allselect 23,'2009-01-14',3 union allselect 24,'2009-01-15',3 union allselect 25,'2009-01-16',1 union allselect 26,'2009-01-17',2 union allselect 27,'2009-01-18',3go--初始化declare @b datetime,@e datetime,@d int,@bt int,@et intselect @b='2009-01-02',@e='2009-01-04',@d=datediff(d,@b,@e)select @bt=times from tb where date=@bselect @et=times from tb where date=@e--保存用于匹配的结果集select rn=identity(int,0,1),* into # from tb where date between @b and @e--求出匹配的所有集合中的第一条记录的idselect id into #1 from tb twhere times=@btand 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 ajoin #1 bon a.id between b.id and b.id+@d/*id date times id----------- ----------------------- ----------- -----------11 2009-01-02 00:00:00.000 1 1112 2009-01-03 00:00:00.000 3 1113 2009-01-04 00:00:00.000 3 1117 2009-01-08 00:00:00.000 1 1718 2009-01-09 00:00:00.000 3 1719 2009-01-10 00:00:00.000 3 1722 2009-01-13 00:00:00.000 1 2223 2009-01-14 00:00:00.000 3 2224 2009-01-15 00:00:00.000 3 22(9 行受影响)*/drop table #drop table #1
[解决办法]
select a.* from tb ajoin #1 bon a.id between b.id and b.id+@d/*id date times----------- ----------------------- -----------11 2009-01-02 00:00:00.000 112 2009-01-03 00:00:00.000 313 2009-01-04 00:00:00.000 317 2009-01-08 00:00:00.000 118 2009-01-09 00:00:00.000 319 2009-01-10 00:00:00.000 322 2009-01-13 00:00:00.000 123 2009-01-14 00:00:00.000 324 2009-01-15 00:00:00.000 3(9 行受影响)*/
[解决办法]
楼上是高手,和我的想法一样,嘿嘿
[解决办法]
WA