有一表用来记录用户开门情况,其中有日期字段表示开门时刻openDate。
由此表新建一个视图,用来表示某天24小时内,各时段的开门次数,Hours,CountOfHour。
条件是用户输入查询日期,得到当天的各时段的开门次数
本人在查询分析器中静态实现如下:
declare @hours int
select @hours=0;
while(@hours<24)
begin
select @hours as Hours,sum(case when(abs(datediff(hh,date,openDate))=@hours) then 1 else 0 end) as CountOfHour
from OpenDoor
where openDate=date
select @hours=@hours+1
end
可以查询得到符合要求的格式,但是不能用于视图中。
请教高手怎么实现这个功能,是否还可以由存储过程实现此功能。
由此表新建一个视图,用来表示某天24小时内,各时段的开门次数,Hours,CountOfHour。
条件是用户输入查询日期,得到当天的各时段的开门次数
本人在查询分析器中静态实现如下:
declare @hours int
select @hours=0;
while(@hours<24)
begin
select @hours as Hours,sum(case when(abs(datediff(hh,date,openDate))=@hours) then 1 else 0 end) as CountOfHour
from OpenDoor
where openDate=date
select @hours=@hours+1
end
可以查询得到符合要求的格式,但是不能用于视图中。
请教高手怎么实现这个功能,是否还可以由存储过程实现此功能。
(
openDate datetime
)
insert into opendoor
select '2006-07-10 00:30:00' union all
select '2006-07-10 00:45:00' union all
select '2006-07-10 01:30:00' union all
select '2006-07-10 02:30:00' union all
select '2006-07-10 03:30:00' union all
select '2006-07-10 04:30:00' union all
select '2006-07-10 06:30:00' union all
select '2006-07-10 07:30:00' union all
select '2006-07-10 10:30:00' union all
select '2006-07-10 12:30:00' union all
select '2006-07-09 00:30:00' union all
select '2006-07-09 02:30:00' union all
select '2006-07-09 03:30:00' union all
select '2006-07-09 04:30:00' union all
select '2006-07-09 07:30:00'
go
create procedure sp_countofhour(@date varchar(10))
as
select top 24 id=identity(int,0,1) into # from syscolumns
select a.id,CountOfHour=count(opendate)
from # a
left outer join
(
select id=datediff(hh,@date,opendate), opendate
from opendoor
where datediff(day,opendate,@date)=0
)b
on a.id=b.id
group by a.id
drop table #
go
exec sp_countofhour '2006-7-10'
--exec sp_countofhour '2006-7-11'go
drop procedure sp_countofhour
drop table opendoor
/*
id CountOfHour
----------- -----------
0 2
1 1
2 1
3 1
4 1
5 0
6 1
7 1
8 0
9 0
10 1
11 0
12 1
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0*/
from ( select convert(varchar(10), openDate, 121) Date,
convert(varchar(13), OpenDate, 121) Hours,
count(convert(varchar(13), OpenDate, 121)) CountofHour
from table
group by convert(varchar(10), openDate, 121),
convert(varchar(13), OpenDate, 121)) DS
from ( select convert(varchar(13), OpenDate, 121) Hours,
count(convert(varchar(13), OpenDate, 121)) CountofHour
from table
group by convert(varchar(13), OpenDate, 121)) DS
这个更好一些
(
openDate datetime
)
insert into opendoor
select '2006-07-10 00:30:00' union all
select '2006-07-10 00:45:00' union all
select '2006-07-10 01:30:00' union all
select '2006-07-10 02:30:00' union all
select '2006-07-10 03:30:00' union all
select '2006-07-10 04:30:00' union all
select '2006-07-10 06:30:00' union all
select '2006-07-10 07:30:00' union all
select '2006-07-10 10:30:00' union all
select '2006-07-10 12:30:00' union all
select '2006-07-09 00:30:00' union all
select '2006-07-09 02:30:00' union all
select '2006-07-09 03:30:00' union all
select '2006-07-09 04:30:00' union all
select '2006-07-09 07:30:00'
CREATE FUNCTION GetTimes (@stardate datetime,@enddate datetime)
RETURNS TABLE
AS
RETURN
(
select count(datediff(hh,convert(varchar(10),@stardate,121),opendate)) as times from opendoor
where opendate>=@stardate and opendate<=@enddate
)
select * from getTimes('2006-7-10 7:00:00','2006-7-10 11:00:00')
drop function getTimes
感谢大大,你的代码在查询分析器中运行正常,但是在应用程序中调用此存储过程时出现问题,错误提示:对象名'#'无效。我试着修改其他,仍然出现对象名××无效的错误提示。
能不能再帮我修改一下,谢谢。本人对数据库不太熟悉。