有表如下:
id Name timeA timeB
1 a 0101 0200
2 b 0321 0082
3 c 0903 1020
....
要计算一天之内每隔5分钟的数量,怎样写这个存储过程?
假设得到数据如下:
time counts
0005 0
0010 1
0015 2
0020 3
...
2355 4
2400 8
谢谢!
id Name timeA timeB
1 a 0101 0200
2 b 0321 0082
3 c 0903 1020
....
要计算一天之内每隔5分钟的数量,怎样写这个存储过程?
假设得到数据如下:
time counts
0005 0
0010 1
0015 2
0020 3
...
2355 4
2400 8
谢谢!
如果是想循環,
set @num=5
while @num<=2400
begin
........
set @num=@num+5
end
declare @Time int
insert CountTable select @Time,count(*) where @Time bewteen TimeA and TimeB
现在就是不清楚怎样得到0005到2400的循环
set @mytime = '0005'
while(cast(@mytime as int ) <= 2400)
begin
insert into 表(time)
select @mytime
set @mytime = right('0000'+ cast(cast(@mytime as int) + 5 as char) , 4)
end
不好意思,如果插入一行是这样写:
declare @Time int
insert CountTable select @Time,count(*) where @Time bewteen TimeA and TimeB
现在就是不清楚怎样得到0005到2400的循环
=====================================================
insert CountTable select @Time,count(*) where @Time bewteen TimeA and TimeB这句有问题吧
那样得到的循环是
5
10
15,而不是
0005
0010
0015,就是说前面两位是表示小时的,后面两位表示分钟,至少是四位数
Set @I=5
Select TOP 1000 ID=Identity(Int,1,1) Into # From syscolumns,SysObjects
Select Right(100+ID*@I/60,2)+Right(100+(ID*@I-ID*@I/60*60),2) As [time] Into #T From #
Where ID<=24*60/@I
Select * From #TDrop Table #,#T
Create ProceDure CountTime(@I Int)
As
Begin
Select TOP 1000 ID=Identity(Int,1,1) Into # From syscolumns,SysObjects
Select Right(100+ID*@I/60,2)+Right(100+(ID*@I-ID*@I/60*60),2) As StartTime,Right(100+(ID+1)*@I/60,2)+Right(100+((ID+1)*@I-(ID+1)*@I/60*60),2) As EndTime Into #T From #
Where ID<24*60/@ISelect
A.StartTime+'-'+A.EndTime As timespane,
Count(B.Time) As counts
From #T A Left Join Rout B
On B.Time Between A.StartTime And A.EndTime
Group By A.StartTime+'-'+A.EndTimeDrop Table #,#T
End
GO
EXEC CountTime 30
EXEC CountTime 15
GO
这样统计的数量是一段时间内车辆进出的数量,而后面我想统计某一时刻区域内的车辆的数量,所以前面的方面不适用.
本来是写了一段批语句回他贴子,....最后让我给他改成存储过程..
Set @I=5
Select TOP 1000 ID=Identity(Int,1,1) Into # From syscolumns,SysObjects
Select Right(100+ID*@I/60,2)+Right(100+(ID*@I-ID*@I/60*60),2) As [time] Into #T From #
Where ID<=24*60/@I
Select * From #T在sybase数据库中,相同功能的语句是什么?我的sybase数据库不支持top语句