Tb_People 表信息：
id
uname era amount
plushtime
1000031 张亮 中年
100000 201404050908
1000032
张亮 中年 100000
201404050913
1000033
天天 年轻 233233
201404050918
1000034
天天 年轻 233233
201404050923
1000035
kimi 年轻 455631
201404050933
1000036
kimi 年轻 455631
201404050938
--分析出每5分钟内有多少人乘车
--时间转化秒不用转化 初始刷卡时间值是20140404090806
UPDATE [Tb_People] SET
shuacardtime=SUBSTRING(plushtime,1,4)+‘-‘+SUBSTRING(plushtime,5,2)+‘-‘+SUBSTRING(plushtime,7,2)+‘
‘+SUBSTRING(plushtime,9,2)+‘:‘+SUBSTRING(plushtime,11,2)
结果为：
id
uname era amount
plushtime
shuacardtime
1000031 张亮 中年
100000 201404050908 2014-04-05
09:08
1000032 张亮 中年
100000 201404050913 2014-04-05
09:13
1000033 天天 年轻
233233 201404050918 2014-04-05
09:18
1000034 天天 年轻
233233 201404050923 2014-04-05
09:23
1000035 kimi 年轻
455631 201404050933 2014-04-05
09:33
1000036 kimi 年轻
455631 201404050938 2014-04-05
09:38
|
1
2
3
4
5
6
7
8
9
10
11
|
--构造区间段declare @dayBegin datetime,@dayEnd datetime
declare @table table(StartTime datetime,EndTime datetime)
set
@dayBegin = ‘2014-04-05 6:00‘
set
@dayEnd = ‘2014-04-06 0:00‘
while @dayBegin <=@dayEnd begin
insert
@table select @dayBegin,dateadd(mi,5,@dayBegin) --每5分钟一个间隔
set
@dayBegin=dateadd(mi,5,@dayBegin)
end
select
* from @table -- 执行后数据如下
|
StartTime
EndTime
2014-04-05
06:00:00.000 2014-04-05 06:05:00.000
2014-04-05
06:05:00.000 2014-04-05 06:10:00.000
2014-04-05
06:10:00.000 2014-04-05 06:15:00.000
2014-04-05
06:15:00.000 2014-04-05 06:20:00.000
2014-04-05
06:20:00.000 2014-04-05 06:25:00.000
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
--区间段分好了，就可以想到每取出一个时间段，然后在乘车时间记录表里查询有多少条记录在该段时间内就行了，可以考虑用游标。declare s cursor
--declare 创建游标
staticfor
select StartTime,EndTime from
@table
--定义变量declare @StartTime datetime,@EndTime datetime
declare @TempTable table(StartTime datetime,EndTime datetime,Number int)
open s --打开游标
fetch next from s into @StartTime,@EndTime --提取上次提取行的下一行
while(@@fetch_status = 0)begin insert
@TempTable select
isnull(max(@StartTime),@StartTime),isnull(max(@EndTime),@EndTime), count(*) from
Tb_People where
shuacardtime > @StartTime and
shuacardtime <=@EndTime
--这里就不能用between and了，不然分隔的时间点上车的人数会在相邻的两个区间段重复计数,另外第一班车的上车时间等于@StartTime 没有计进去，这里不影响总体分析，当然可以做个标记，读一个区间段时用between...and...就可以了 fetch
next from s into @StartTime,@EndTime
endclose s --关闭游标
deallocate
s --删除游标，释放资源
select
* from @TempTable
|
执行结果如下：
StartTime
EndTime
Number
2014-04-05 09:05:00.000 2014-04-05
09:10:00.000 1
2014-04-05 09:10:00.000
2014-04-05 09:15:00.000 1
2014-04-05
09:15:00.000 2014-04-05 09:20:00.000
1
2014-04-05 09:20:00.000 2014-04-05
09:25:00.000 1
2014-04-05 09:25:00.000
2014-04-05 09:30:00.000 0
2014-04-05
09:30:00.000 2014-04-05 09:35:00.000 1