表数据结构如下:
RoomID 房间编号 startTime开始时间 endTime结束时间 RoomState 状态 传ID 、和开始时间、结束时间 , 查询出在这个时间段内每天RoomState的状态值
哪位大虾 知道的 快点告诉我
RoomID 房间编号 startTime开始时间 endTime结束时间 RoomState 状态 传ID 、和开始时间、结束时间 , 查询出在这个时间段内每天RoomState的状态值
哪位大虾 知道的 快点告诉我
RoomState
from
tb
where
时间 between startTime and endTime
and
RoomID=???
from tb
where startTime >=@startTime
and endTime<=@endTime
and RoomID=@ID
Roomtype表中数据RoomID StateTime EndTime RoomState
A001S01 2009-07-23 2009-07-27 0
A001s01 2009-07-25 2009-07-27 1
A001S02 2009-07-02 2009-07-21 0我传入 RoomID @stateTime=2009-07-23 @endTime=2009-07-27 查询出来的结果要像这样
Time Roomstate
2009-07-23 0
2009-07-24 0
2009-07-25 1
2009-07-26 1
2009-07-27 1
declare @n int
set @n=1
while (@statetime<=@endtime)
begin
insert into #
values(@statetime)
set @statetime= dateadd(day,1,@statetime)
end
select times,(select RoomState from Roomtype where times between StateTime and EndTime ) as zhuangtai
from #
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('Roomtype') IS NOT NULL
DROP TABLE Roomtype
GO
CREATE TABLE Roomtype(RoomID varchar(10),StateTime datetime, EndTime datetime,RoomState int )
go
insert Roomtype SELECT
'A001S01' , '2009-07-23' ,'2009-07-24' , 0 UNION ALL SELECT
'A001s01' , '2009-07-25' , '2009-07-27' , 1 UNION ALL SELECT
'A001S02' , '2009-07-02' , '2009-07-21' , 0
go
declare @statetime datetime,@endtime datetime
set @statetime='2009-07-23'
set @endtime='2009-07-27'
create table #(times datetime)
declare @n int
set @n=1
while (@statetime<=@endtime)
begin
insert into #
values(@statetime)
set @statetime= dateadd(day,1,@statetime)
end
select times=CONVERT(varchar(10),times,120),RoomState
from # cross join Roomtype
where times between StateTime and EndTime
go
drop table #
/*------------
times RoomState
---------- -----------
2009-07-23 0
2009-07-24 0
2009-07-25 1
2009-07-26 1
2009-07-27 1
-------*/