表结构:
id i_id i_time i_pvcount i_uvcount
162 13974509 2009-04-24 00:59:00 23 8
163 13974509 2009-04-24 01:59:00 45 7
164 13974509 2009-04-24 02:30:00 2 2
165 13974509 2009-04-24 03:42:00 8 2
166 13974509 2009-04-24 04:29:00 2 2
167 13974509 2009-04-24 05:30:00 7 1
168 13974509 2009-04-24 07:39:00 5 1
169 13974509 2009-04-24 08:58:00 12 6
170 13974509 2009-04-24 09:59:00 41 15
171 13974509 2009-04-24 10:59:00 84 24
173 13974509 2009-04-24 11:59:00 140 28
175 13974509 2009-04-24 12:59:00 63 20
176 13974509 2009-04-24 16:55:00 310 107
177 13974509 2009-04-24 15:00:00 2 0
178 13974509 2009-04-24 15:00:00 1 0
179 13974509 2009-04-24 15:00:00 1 0
181 13974509 2009-04-24 17:59:00 51 16
182 13974509 2009-04-24 22:57:00 425 91
183 13974509 2009-04-24 22:00:00 1 0
185 13974509 2009-04-24 23:59:00 70 22输入一个日期,显示那个日期的24小时段的 i_pvcount,i_uvcount例如: 0:00-1:00 23 565
1:00-2:00 25 11
2:00-3:00 45 3
... .. ..
... .. ..
23:00-24:00 23 23
像这样的存储过程怎么写啊~
id i_id i_time i_pvcount i_uvcount
162 13974509 2009-04-24 00:59:00 23 8
163 13974509 2009-04-24 01:59:00 45 7
164 13974509 2009-04-24 02:30:00 2 2
165 13974509 2009-04-24 03:42:00 8 2
166 13974509 2009-04-24 04:29:00 2 2
167 13974509 2009-04-24 05:30:00 7 1
168 13974509 2009-04-24 07:39:00 5 1
169 13974509 2009-04-24 08:58:00 12 6
170 13974509 2009-04-24 09:59:00 41 15
171 13974509 2009-04-24 10:59:00 84 24
173 13974509 2009-04-24 11:59:00 140 28
175 13974509 2009-04-24 12:59:00 63 20
176 13974509 2009-04-24 16:55:00 310 107
177 13974509 2009-04-24 15:00:00 2 0
178 13974509 2009-04-24 15:00:00 1 0
179 13974509 2009-04-24 15:00:00 1 0
181 13974509 2009-04-24 17:59:00 51 16
182 13974509 2009-04-24 22:57:00 425 91
183 13974509 2009-04-24 22:00:00 1 0
185 13974509 2009-04-24 23:59:00 70 22输入一个日期,显示那个日期的24小时段的 i_pvcount,i_uvcount例如: 0:00-1:00 23 565
1:00-2:00 25 11
2:00-3:00 45 3
... .. ..
... .. ..
23:00-24:00 23 23
像这样的存储过程怎么写啊~
解决方案 »
- 我的网站SQL连接报错
- sqlserver连结myeclipse 那里错了啊
- sql 2005中xml类型变量
- sql 中 in 的用法 高手指点
- 怎么获得逻辑表Inserted里的值(触发器急急)
- 维护别人的项目 sql server中的注释都变成乱码了咋么办
- SQL SERVER 2000 将系统内存几乎全部吃掉???
- DBMS MMS Micorsoft SQL SERVER6.x is not Supported in your current installtion.
- 对不起,没有分了,请大家给我看一下代码,非常感谢
- 关于注册ODBC,用InStallFiled.
- 【SQL】下面这种情况中,不使用Group By 如何实现.
- ◆sql语句递归查询和删除问题◆
insert tb values(162, 13974509,'2009-04-24 00:59:00',23, 8)
insert tb values(163, 13974509,'2009-04-24 01:59:00',45, 7)
insert tb values(164, 13974509,'2009-04-24 02:30:00',2 ,2)
insert tb values(165, 13974509,'2009-04-24 03:42:00',8 ,2)
insert tb values(166, 13974509,'2009-04-24 04:29:00',2 ,2)
insert tb values(167, 13974509,'2009-04-24 05:30:00',7 ,1)
insert tb values(168, 13974509,'2009-04-24 07:39:00',5 ,1)
insert tb values(169, 13974509,'2009-04-24 08:58:00',12, 6)
insert tb values(170, 13974509,'2009-04-24 09:59:00',41, 15)
insert tb values(171, 13974509,'2009-04-24 10:59:00',84, 24)
insert tb values(173, 13974509,'2009-04-24 11:59:00',140, 28)
insert tb values(175, 13974509,'2009-04-24 12:59:00',63, 20)
insert tb values(176, 13974509,'2009-04-24 16:55:00',310, 107)
insert tb values(177, 13974509,'2009-04-24 15:00:00',2 ,0)
insert tb values(178, 13974509,'2009-04-24 15:00:00',1 ,0)
insert tb values(179, 13974509,'2009-04-24 15:00:00',1 ,0)
insert tb values(181, 13974509,'2009-04-24 17:59:00',51, 16)
insert tb values(182, 13974509,'2009-04-24 22:57:00',425, 91)
insert tb values(183, 13974509,'2009-04-24 22:00:00',1 ,0)
insert tb values(185, 13974509,'2009-04-24 23:59:00',70, 22)select Datename(hh,i_time)+':00-'+cast(cast(Datename(hh,i_time) as int)+1 as varchar(2))+':00' 小时,
sum(i_pvcount) i_pvcount,sum(i_uvcount) i_uvcount from tb
where convert(varchar(10),i_time,120)='2009-04-24' --日期
group by Datename(hh,i_time)
order by cast(Datename(hh,i_time) as int)/*
小时 i_pvcount i_uvcount
--------------------------------------- ----------- -----------
0:00-1:00 23 8
1:00-2:00 45 7
2:00-3:00 2 2
3:00-4:00 8 2
4:00-5:00 2 2
5:00-6:00 7 1
7:00-8:00 5 1
8:00-9:00 12 6
9:00-10:00 41 15
10:00-11:00 84 24
11:00-12:00 140 28
12:00-13:00 63 20
15:00-16:00 4 0
16:00-17:00 310 107
17:00-18:00 51 16
22:00-23:00 426 91
23:00-24:00 70 22
*/
insert tb select 162,'13974509','2009-04-24 00:59:00',23,8
union all select 163,'13974509','2009-04-24 01:59:00',45,7
union all select 164, '13974509',' 2009-04-24 02:30:00', 2, 2
union all select 165, '13974509',' 2009-04-24 03:42:00', 8, 2
union all select 166, '13974509', '2009-04-24 04:29:00', 2, 2
union all select 167, '13974509', '2009-04-24 05:30:00', 7, 1
union all select 168, '13974509', '2009-04-24 07:39:00', 5, 1
union all select 169, '13974509', '2009-04-24 08:58:00', 12, 6
union all select 170, '13974509', '2009-04-24 09:59:00', 41, 15
union all select 171, '13974509', '2009-04-24 10:59:00', 84, 24
union all select 173, '13974509', '2009-04-24 11:59:00', 140, 28
union all select 175, '13974509', '2009-04-24 12:59:00', 63 ,20
union all select 176, '13974509', '2009-04-24 16:55:00', 310, 107
union all select 177, '13974509', '2009-04-24 15:00:00', 2 ,0
union all select 178, '13974509', '2009-04-24 15:00:00', 1 ,0
union all select 179, '13974509', '2009-04-24 15:00:00', 1 ,0
union all select 181, '13974509', '2009-04-24 17:59:00', 51 ,16
union all select 182, '13974509', '2009-04-24 22:57:00', 425 ,91
union all select 183, '13974509', '2009-04-24 22:00:00', 1 ,0
union all select 185, '13974509', '2009-04-24 23:59:00', 70 ,22 select * from tb--创建函数
if object_id('fun_select') is not null
drop function fun_select
gocreate function fun_select(@date datetime)
returns @temptable table([time] varchar(20),i_pvcount int,i_uvcount int)
as
begin
declare @t1 datetime,@t2 datetime
set @date=convert(char(10),@date,120)
select @t1=@date,@t2=@date
while(@t1<dateadd(day,1,@date))
begin
set @t2=dateadd(hour,1,@t1)
insert @temptable
select convert(char(8),@t1,108)+'-'+convert(char(8),@t2,108),
isnull(sum(i_pvcount),0),isnull(sum(i_uvcount),0)
from tb where i_time between @t1 and @t2
set @t1=@t2
end
return
end
goselect * from dbo.fun_select('2009-4-24')--删除函数和表
drop table tb
drop function fun_select
/*
所影响的行数为 20 行)id i_id i_time i_pvcount i_uvcount
----------- ------------ ------------------------------------------------------ ----------- -----------
162 13974509 2009-04-24 00:59:00.000 23 8
163 13974509 2009-04-24 01:59:00.000 45 7
164 13974509 2009-04-24 02:30:00.000 2 2
165 13974509 2009-04-24 03:42:00.000 8 2
166 13974509 2009-04-24 04:29:00.000 2 2
167 13974509 2009-04-24 05:30:00.000 7 1
168 13974509 2009-04-24 07:39:00.000 5 1
169 13974509 2009-04-24 08:58:00.000 12 6
170 13974509 2009-04-24 09:59:00.000 41 15
171 13974509 2009-04-24 10:59:00.000 84 24
173 13974509 2009-04-24 11:59:00.000 140 28
175 13974509 2009-04-24 12:59:00.000 63 20
176 13974509 2009-04-24 16:55:00.000 310 107
177 13974509 2009-04-24 15:00:00.000 2 0
178 13974509 2009-04-24 15:00:00.000 1 0
179 13974509 2009-04-24 15:00:00.000 1 0
181 13974509 2009-04-24 17:59:00.000 51 16
182 13974509 2009-04-24 22:57:00.000 425 91
183 13974509 2009-04-24 22:00:00.000 1 0
185 13974509 2009-04-24 23:59:00.000 70 22(所影响的行数为 20 行)time i_pvcount i_uvcount
-------------------- ----------- -----------
00:00:00-01:00:00 23 8
01:00:00-02:00:00 45 7
02:00:00-03:00:00 2 2
03:00:00-04:00:00 8 2
04:00:00-05:00:00 2 2
05:00:00-06:00:00 7 1
06:00:00-07:00:00 0 0
07:00:00-08:00:00 5 1
08:00:00-09:00:00 12 6
09:00:00-10:00:00 41 15
10:00:00-11:00:00 84 24
11:00:00-12:00:00 140 28
12:00:00-13:00:00 63 20
13:00:00-14:00:00 0 0
14:00:00-15:00:00 4 0
15:00:00-16:00:00 4 0
16:00:00-17:00:00 310 107
17:00:00-18:00:00 51 16
18:00:00-19:00:00 0 0
19:00:00-20:00:00 0 0
20:00:00-21:00:00 0 0
21:00:00-22:00:00 1 0
22:00:00-23:00:00 426 91
23:00:00-00:00:00 70 22(所影响的行数为 24 行)*/
insert tb select 162,'13974509','2009-04-24 00:59:00',23,8
union all select 163,'13974509','2009-04-24 01:59:00',45,7
union all select 164, '13974509',' 2009-04-24 02:30:00', 2, 2
union all select 165, '13974509',' 2009-04-24 03:42:00', 8, 2
union all select 166, '13974509', '2009-04-24 04:29:00', 2, 2
union all select 167, '13974509', '2009-04-24 05:30:00', 7, 1
union all select 168, '13974509', '2009-04-24 07:39:00', 5, 1
union all select 169, '13974509', '2009-04-24 08:58:00', 12, 6
union all select 170, '13974509', '2009-04-24 09:59:00', 41, 15
union all select 171, '13974509', '2009-04-24 10:59:00', 84, 24
union all select 173, '13974509', '2009-04-24 11:59:00', 140, 28
union all select 175, '13974509', '2009-04-24 12:59:00', 63 ,20
union all select 176, '13974509', '2009-04-24 16:55:00', 310, 107
union all select 177, '13974509', '2009-04-24 15:00:00', 2 ,0
union all select 178, '13974509', '2009-04-24 15:00:00', 1 ,0
union all select 179, '13974509', '2009-04-24 15:00:00', 1 ,0
union all select 181, '13974509', '2009-04-24 17:59:00', 51 ,16
union all select 182, '13974509', '2009-04-24 22:57:00', 425 ,91
union all select 183, '13974509', '2009-04-24 22:00:00', 1 ,0
union all select 185, '13974509', '2009-04-24 23:59:00', 70 ,22 select * from tb--创建函数
if object_id('fun_select') is not null
drop function fun_select
gocreate function fun_select(@date datetime)
returns @temptable table([time] varchar(20),i_pvcount int,i_uvcount int)
as
begin
declare @t1 datetime,@t2 datetime set @date=convert(char(10),@date,120)
set @t1=@date
while(@t1<dateadd(day,1,@date))
begin
set @t2=dateadd(hour,1,@t1)
insert @temptable
select convert(char(5),@t1,108)+'-'+convert(char(5),@t2,108),
isnull(sum(i_pvcount),0),isnull(sum(i_uvcount),0)
from tb where i_time between @t1 and @t2 set @t1=@t2
end
return
end
goselect * from dbo.fun_select('2009-4-24')--删除函数和表
drop table tb
drop function fun_select
/*
(所影响的行数为 20 行)id i_id i_time i_pvcount i_uvcount
----------- ------------ ------------------------------------------------------ ----------- -----------
162 13974509 2009-04-24 00:59:00.000 23 8
163 13974509 2009-04-24 01:59:00.000 45 7
164 13974509 2009-04-24 02:30:00.000 2 2
165 13974509 2009-04-24 03:42:00.000 8 2
166 13974509 2009-04-24 04:29:00.000 2 2
167 13974509 2009-04-24 05:30:00.000 7 1
168 13974509 2009-04-24 07:39:00.000 5 1
169 13974509 2009-04-24 08:58:00.000 12 6
170 13974509 2009-04-24 09:59:00.000 41 15
171 13974509 2009-04-24 10:59:00.000 84 24
173 13974509 2009-04-24 11:59:00.000 140 28
175 13974509 2009-04-24 12:59:00.000 63 20
176 13974509 2009-04-24 16:55:00.000 310 107
177 13974509 2009-04-24 15:00:00.000 2 0
178 13974509 2009-04-24 15:00:00.000 1 0
179 13974509 2009-04-24 15:00:00.000 1 0
181 13974509 2009-04-24 17:59:00.000 51 16
182 13974509 2009-04-24 22:57:00.000 425 91
183 13974509 2009-04-24 22:00:00.000 1 0
185 13974509 2009-04-24 23:59:00.000 70 22(所影响的行数为 20 行)time i_pvcount i_uvcount
-------------------- ----------- -----------
00:00-01:00 23 8
01:00-02:00 45 7
02:00-03:00 2 2
03:00-04:00 8 2
04:00-05:00 2 2
05:00-06:00 7 1
06:00-07:00 0 0
07:00-08:00 5 1
08:00-09:00 12 6
09:00-10:00 41 15
10:00-11:00 84 24
11:00-12:00 140 28
12:00-13:00 63 20
13:00-14:00 0 0
14:00-15:00 4 0
15:00-16:00 4 0
16:00-17:00 310 107
17:00-18:00 51 16
18:00-19:00 0 0
19:00-20:00 0 0
20:00-21:00 0 0
21:00-22:00 1 0
22:00-23:00 426 91
23:00-00:00 70 22(所影响的行数为 24 行)
*/
drop table tb
GO
create table tb(id int,i_id int,i_time datetime,i_pvcount int,i_uvcount int)insert tb values(162, 13974509,'2009-04-24 00:59:00',23, 8)
insert tb values(163, 13974509,'2009-04-24 01:59:00',45, 7)
insert tb values(164, 13974509,'2009-04-24 02:30:00',2 ,2)
insert tb values(165, 13974509,'2009-04-24 03:42:00',8 ,2)
insert tb values(166, 13974509,'2009-04-24 04:29:00',2 ,2)
insert tb values(167, 13974509,'2009-04-24 05:30:00',7 ,1)
insert tb values(168, 13974509,'2009-04-24 07:39:00',5 ,1)
insert tb values(169, 13974509,'2009-04-24 08:58:00',12, 6)
insert tb values(170, 13974509,'2009-04-24 09:59:00',41, 15)
insert tb values(171, 13974509,'2009-04-24 10:59:00',84, 24)
insert tb values(173, 13974509,'2009-04-24 11:59:00',140, 28)
insert tb values(175, 13974509,'2009-04-24 12:59:00',63, 20)
insert tb values(176, 13974509,'2009-04-24 16:55:00',310, 107)
insert tb values(177, 13974509,'2009-04-24 15:00:00',2 ,0)
insert tb values(178, 13974509,'2009-04-24 15:00:00',1 ,0)
insert tb values(179, 13974509,'2009-04-24 15:00:00',1 ,0)
insert tb values(181, 13974509,'2009-04-24 17:59:00',51, 16)
insert tb values(182, 13974509,'2009-04-24 22:57:00',425, 91)
insert tb values(183, 13974509,'2009-04-24 22:00:00',1 ,0)
insert tb values(185, 13974509,'2009-04-24 23:59:00',70, 22)if exists(select 1 from sys.procedures where name like 'SP_Gethour_Con')
drop proc SP_Gethour_Con
GO
create proc SP_Gethour_Con
@date datetime
as
set nocount on
declare @t table(hh int)
declare @i int
set @i=0
while @i<24
begin
insert @t values(@i)
set @i=@i+1
end;
with a as
(select sum(i_pvcount) i_pvcount,sum(i_uvcount) i_uvcount,cast(Datename(hh,i_time) as int) hh
from tb where convert(varchar(10),i_time,120)=convert(varchar(10),@date,120) --日期
group by Datename(hh,i_time))
select cast(b.hh as varchar(2))+':00-'+cast(b.hh+1 as varchar(2))+':00' 时间,
isnull(a.i_pvcount,0) i_pvcount,
isnull(a.i_uvcount,0) i_uvcount
from a
right join @t b on a.hh=b.hh order by b.hh
set nocount off
go
--调用
exec SP_Gethour_Con '2009-04-24'
/*
结果:
时间 i_pvcount i_uvcount
----------- ----------- -----------
0:00-1:00 23 8
1:00-2:00 45 7
2:00-3:00 2 2
3:00-4:00 8 2
4:00-5:00 2 2
5:00-6:00 7 1
6:00-7:00 0 0
7:00-8:00 5 1
8:00-9:00 12 6
9:00-10:00 41 15
10:00-11:00 84 24
11:00-12:00 140 28
12:00-13:00 63 20
13:00-14:00 0 0
14:00-15:00 0 0
15:00-16:00 4 0
16:00-17:00 310 107
17:00-18:00 51 16
18:00-19:00 0 0
19:00-20:00 0 0
20:00-21:00 0 0
21:00-22:00 0 0
22:00-23:00 426 91
23:00-24:00 70 22
*/