假设有这样的数据: AA 2005-12-15 09:55:24 AA 2005-12-15 09:55:24 AA 2005-12-15 09:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 11:55:24 AA 2005-12-15 11:55:24 AA 2005-12-15 11:55:24 AA 2005-12-15 11:55:24 这样的效果 9:00-10:00 AA 2005-12-15 09:55:24 AA 2005-12-15 09:55:24 AA 2005-12-15 09:55:24 10:00-11:00 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 AA 2005-12-15 10:55:24 11:00-12:00 AA 2005-12-15 11:55:24 AA 2005-12-15 11:55:24 写出sql句和步骤,我是新手,喊你sql王子,sql_vip,sql牛比王,sql克星 AA 2005-12-15 11:55:24
--测试环境 create table tiaa (f1 nvarchar(10), ti_me smalldatetime) insert into tiaa select 'AA', '2005-12-15 09:55:24' union all select 'AA', '2005-12-15 09:55:24' union all select 'AA', '2005-12-15 09:55:24' union all select 'AA', '2005-12-15 10:55:24' union all select 'AA', '2005-12-15 10:55:24' union all select 'AA', '2005-12-15 10:55:24' union all select 'AA', '2005-12-15 10:55:24' union all select 'AA', '2005-12-15 11:55:24' union all select 'AA', '2005-12-15 11:55:24' union all select 'AA', '2005-12-15 11:55:24' union all select 'AA', '2005-12-15 11:55:24' go --建立存储过程 create proc time_proc @btime smalldatetime, @etime smalldatetime as select * from tiaa where datepart(year,ti_me) = datepart(year,@btime) and datepart(month,ti_me) = datepart(month,@btime) and datepart(day,ti_me) = datepart(day,@btime) and datepart(hour,ti_me) >= datepart(hour,@btime) and datepart(hour,ti_me) < datepart(hour,@etime) go --执行存储过程 exec time_proc '2005-12-15 9:00','2005-12-15 10:00' exec time_proc '2005-12-15 10:00','2005-12-15 11:00' exec time_proc '2005-12-15 11:00','2005-12-15 12:00' --希望楼主以后不要起这样标题了。 有损自己的尊严!!!!!!!
由傳入時間頭時間尾求出(變量nPeriod記錄)時段數,例句 declare @nPeriod tinyint select @nPeriod = DATEDIFF(hour, @tStart, @tEnd) . 然後寫一個循環求動態sql語句 例句 declare @strSql varchar(2000),@i tineyint=1] select @tStart= @tStart,@tEnd =dateadd(hour,1,@tStart) while @i<@nPeriod begin select @strSql =@strSql + ' union all select * from T [Your Condition] '----Your Condition如 Ydatetime between @tStart and @tEnd select @strSql =@strSql + '' select @i=@i+1 end exec(@strSql )
定义一个表变量,插入 8:00-9:00
9:00-10:00
10:00-11:00
这几条数据,然后再JOIN这个表变量一下就行了。-------------------
http://chinadba.cn
最具实战经验的数据库优化,管理,设计,培训网
用存储过程 应该不难实现hehe 真的不敢做了 近来SQL版怎么多了些笑星 ............
AA 2005-12-15 09:55:24
AA 2005-12-15 09:55:24
AA 2005-12-15 09:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 11:55:24
AA 2005-12-15 11:55:24
AA 2005-12-15 11:55:24
AA 2005-12-15 11:55:24
这样的效果
9:00-10:00 AA 2005-12-15 09:55:24
AA 2005-12-15 09:55:24
AA 2005-12-15 09:55:24
10:00-11:00 AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
AA 2005-12-15 10:55:24
11:00-12:00 AA 2005-12-15 11:55:24
AA 2005-12-15 11:55:24
写出sql句和步骤,我是新手,喊你sql王子,sql_vip,sql牛比王,sql克星
AA 2005-12-15 11:55:24
create table tiaa
(f1 nvarchar(10),
ti_me smalldatetime)
insert into tiaa
select 'AA', '2005-12-15 09:55:24' union all
select 'AA', '2005-12-15 09:55:24' union all
select 'AA', '2005-12-15 09:55:24' union all
select 'AA', '2005-12-15 10:55:24' union all
select 'AA', '2005-12-15 10:55:24' union all
select 'AA', '2005-12-15 10:55:24' union all
select 'AA', '2005-12-15 10:55:24' union all
select 'AA', '2005-12-15 11:55:24' union all
select 'AA', '2005-12-15 11:55:24' union all
select 'AA', '2005-12-15 11:55:24' union all
select 'AA', '2005-12-15 11:55:24'
go
--建立存储过程
create proc time_proc
@btime smalldatetime,
@etime smalldatetime
as
select * from tiaa where datepart(year,ti_me) = datepart(year,@btime) and
datepart(month,ti_me) = datepart(month,@btime) and
datepart(day,ti_me) = datepart(day,@btime) and
datepart(hour,ti_me) >= datepart(hour,@btime) and
datepart(hour,ti_me) < datepart(hour,@etime)
go
--执行存储过程
exec time_proc '2005-12-15 9:00','2005-12-15 10:00'
exec time_proc '2005-12-15 10:00','2005-12-15 11:00'
exec time_proc '2005-12-15 11:00','2005-12-15 12:00'
--希望楼主以后不要起这样标题了。 有损自己的尊严!!!!!!!
然後寫一個循環求動態sql語句
例句 declare @strSql varchar(2000),@i tineyint=1]
select @tStart= @tStart,@tEnd =dateadd(hour,1,@tStart)
while @i<@nPeriod
begin
select @strSql =@strSql + ' union all select * from T [Your Condition] '----Your Condition如 Ydatetime between @tStart and @tEnd
select @strSql =@strSql + ''
select @i=@i+1
end
exec(@strSql )
http://chinadba.cn
要不是推广我的数据库优化网站,我真不愿意回答这样的问题