哎，终于做好了，要了我的老命了！哎！declare @sql varchar(8000),@i int
/*--------------------------------------------------------*/
select @sql='',@i=23
while @i>=0
select @sql=',hour'+cast(@i as varchar)+' int'+@sql,@i=@i-1select @sql='create table t(companyid varchar(20)'+@sql+',d_time datetime)'select @sql=@sql+' insert into t select ''A001'' 'select @i=24while @i>0
select @sql=@sql+','+right(cast(RAND(@i) as varchar),2),
@i=@i-1select @sql=@sql+',''2005-10-19'' union all select ''A001'''select @i=100while @i>76
select @sql=@sql+','+right(cast(RAND(@i) as varchar),2),
@i=@i-1select @sql=@sql+',''2005-10-20'''exec(@sql)
/*--------------------以上数据是动态生成表格和数据的程序段-------------------*//*----------------------这里开始是程序段------------------------------------*/declare @t datetime
select @t='2005-10-20 8:00'
select * from t
select @sql=''select @sql='select * from (',@i=23
while @i>=0
select @sql=@sql+'
select companyid,hour'+cast(@i as varchar)+' as num,d_time=dateadd(hour,'+cast(@i as varchar)+',d_time) from t
union all'
,@i=@i-1select @sql=reverse(stuff(reverse(@sql),1,10,''))+'
) a
where (datepart(hour,d_time) between 0 and datepart(hour,'''+convert(varchar,@t,120)+''') and
convert(char(10),d_time,120)=convert(char(10),'''+convert(varchar,@t,120)+''',120))
or (datediff(hour,d_time,'''+convert(char(11),dateadd(d,-1,@t),120)+'23:00'')<16
and convert(char(10),d_time,120)='''+convert(char(10),dateadd(d,-1,@t),120)+''')
order by d_time'
exec(@sql)
drop table t/*----------------下面是测试和结果数据---------------------*/（所影响的行数为 2 行）companyid hour0 hour1 hour2 hour3 hour4 hour5 hour6 hour7 hour8 hour9 hour10 hour11 hour12 hour13 hour14 hour15 hour16 hour17 hour18 hour19 hour20 hour21 hour22 hour23 d_time
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------------------------------------------
A001 21 2 83 65 46 27 9 89 71 53 34 16 97 78 76 41 22 4 85 67 48 29 11 92 2005-10-19 00:00:00.000
A001 37 18 99 81 62 43 25 6 88 69 25 32 13 94 76 57 39 12 1 83 64 45 27 8 2005-10-20 00:00:00.000（所影响的行数为 2 行）companyid num d_time
-------------------- ----------- ------------------------------------------------------
A001 71 2005-10-19 08:00:00.000
A001 53 2005-10-19 09:00:00.000
A001 34 2005-10-19 10:00:00.000
A001 16 2005-10-19 11:00:00.000
A001 97 2005-10-19 12:00:00.000
A001 78 2005-10-19 13:00:00.000
A001 76 2005-10-19 14:00:00.000
A001 41 2005-10-19 15:00:00.000
A001 22 2005-10-19 16:00:00.000
A001 4 2005-10-19 17:00:00.000
A001 85 2005-10-19 18:00:00.000
A001 67 2005-10-19 19:00:00.000
A001 48 2005-10-19 20:00:00.000
A001 29 2005-10-19 21:00:00.000
A001 11 2005-10-19 22:00:00.000
A001 92 2005-10-19 23:00:00.000
A001 37 2005-10-20 00:00:00.000
A001 18 2005-10-20 01:00:00.000
A001 99 2005-10-20 02:00:00.000
A001 81 2005-10-20 03:00:00.000
A001 62 2005-10-20 04:00:00.000
A001 43 2005-10-20 05:00:00.000
A001 25 2005-10-20 06:00:00.000
A001 6 2005-10-20 07:00:00.000
A001 88 2005-10-20 08:00:00.000
/*--------------------------------------------------------*/
select @sql='',@i=23
while @i>=0
select @sql=',hour'+cast(@i as varchar)+' int'+@sql,@i=@i-1select @sql='create table t(companyid varchar(20)'+@sql+',d_time datetime)'select @sql=@sql+' insert into t select ''A001'' 'select @i=24while @i>0
select @sql=@sql+','+right(cast(RAND(@i) as varchar),2),
@i=@i-1select @sql=@sql+',''2005-10-19'' union all select ''A001'''select @i=100while @i>76
select @sql=@sql+','+right(cast(RAND(@i) as varchar),2),
@i=@i-1select @sql=@sql+',''2005-10-20'''exec(@sql)
/*--------------------以上数据是动态生成表格和数据的程序段-------------------*//*----------------------这里开始是程序段------------------------------------*/declare @t datetime
select @t='2005-10-20 8:00'
select * from t
select @sql=''select @sql='select * from (',@i=23
while @i>=0
select @sql=@sql+'
select companyid,hour'+cast(@i as varchar)+' as num,d_time=dateadd(hour,'+cast(@i as varchar)+',d_time) from t
union all'
,@i=@i-1select @sql=reverse(stuff(reverse(@sql),1,10,''))+'
) a
where (datepart(hour,d_time) between 0 and datepart(hour,'''+convert(varchar,@t,120)+''') and
convert(char(10),d_time,120)=convert(char(10),'''+convert(varchar,@t,120)+''',120))
or (datediff(hour,d_time,'''+convert(char(11),dateadd(d,-1,@t),120)+'23:00'')<16
and convert(char(10),d_time,120)='''+convert(char(10),dateadd(d,-1,@t),120)+''')
order by d_time'
exec(@sql)
drop table t/*----------------下面是测试和结果数据---------------------*/（所影响的行数为 2 行）companyid hour0 hour1 hour2 hour3 hour4 hour5 hour6 hour7 hour8 hour9 hour10 hour11 hour12 hour13 hour14 hour15 hour16 hour17 hour18 hour19 hour20 hour21 hour22 hour23 d_time
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------------------------------------------
A001 21 2 83 65 46 27 9 89 71 53 34 16 97 78 76 41 22 4 85 67 48 29 11 92 2005-10-19 00:00:00.000
A001 37 18 99 81 62 43 25 6 88 69 25 32 13 94 76 57 39 12 1 83 64 45 27 8 2005-10-20 00:00:00.000（所影响的行数为 2 行）companyid num d_time
-------------------- ----------- ------------------------------------------------------
A001 71 2005-10-19 08:00:00.000
A001 53 2005-10-19 09:00:00.000
A001 34 2005-10-19 10:00:00.000
A001 16 2005-10-19 11:00:00.000
A001 97 2005-10-19 12:00:00.000
A001 78 2005-10-19 13:00:00.000
A001 76 2005-10-19 14:00:00.000
A001 41 2005-10-19 15:00:00.000
A001 22 2005-10-19 16:00:00.000
A001 4 2005-10-19 17:00:00.000
A001 85 2005-10-19 18:00:00.000
A001 67 2005-10-19 19:00:00.000
A001 48 2005-10-19 20:00:00.000
A001 29 2005-10-19 21:00:00.000
A001 11 2005-10-19 22:00:00.000
A001 92 2005-10-19 23:00:00.000
A001 37 2005-10-20 00:00:00.000
A001 18 2005-10-20 01:00:00.000
A001 99 2005-10-20 02:00:00.000
A001 81 2005-10-20 03:00:00.000
A001 62 2005-10-20 04:00:00.000
A001 43 2005-10-20 05:00:00.000
A001 25 2005-10-20 06:00:00.000
A001 6 2005-10-20 07:00:00.000
A001 88 2005-10-20 08:00:00.000
select @t='2005-10-20 08:00'
select * from t
select @sql=''select @sql='select * from (',@i=23
while @i>=0
select @sql=@sql+'
select companyid,hour'+cast(@i as varchar)+' as num,d_time=dateadd(hour,'+cast(@i as varchar)+',d_time) from t
union all'
,@i=@i-1select @sql=reverse(stuff(reverse(@sql),1,10,''))+'
) a
where (datepart(hour,d_time) between 0 and datepart(hour,'''+convert(varchar,@t,120)+''') and
convert(char(10),d_time,120)=convert(char(10),'''+convert(varchar,@t,120)+''',120))
or (datediff(hour,d_time,'''+convert(char(11),dateadd(d,-1,@t),120)+'23:00'')<
'+cast(23-datepart(hour,@t) as varchar)+'
and convert(char(10),d_time,120)='''+convert(char(10),dateadd(d,-1,@t),120)+''')
order by d_time'
exec(@sql)
改了一下，刚才有个小问题。我测试了一下，对于'2005-10-20 08:00'
companyid num d_time
-------------------- ----------- ------------------------------------------------------
A001 53 2005-10-19 09:00:00.000
A001 34 2005-10-19 10:00:00.000
A001 16 2005-10-19 11:00:00.000
A001 97 2005-10-19 12:00:00.000
A001 78 2005-10-19 13:00:00.000
A001 76 2005-10-19 14:00:00.000
A001 41 2005-10-19 15:00:00.000
A001 22 2005-10-19 16:00:00.000
A001 4 2005-10-19 17:00:00.000
A001 85 2005-10-19 18:00:00.000
A001 67 2005-10-19 19:00:00.000
A001 48 2005-10-19 20:00:00.000
A001 29 2005-10-19 21:00:00.000
A001 11 2005-10-19 22:00:00.000
A001 92 2005-10-19 23:00:00.000
A001 37 2005-10-20 00:00:00.000
A001 18 2005-10-20 01:00:00.000
A001 99 2005-10-20 02:00:00.000
A001 81 2005-10-20 03:00:00.000
A001 62 2005-10-20 04:00:00.000
A001 43 2005-10-20 05:00:00.000
A001 25 2005-10-20 06:00:00.000
A001 6 2005-10-20 07:00:00.000
A001 88 2005-10-20 08:00:00.000和'2005-10-20 23:00'的数据companyid num d_time
-------------------- ----------- ------------------------------------------------------
A001 37 2005-10-20 00:00:00.000
A001 18 2005-10-20 01:00:00.000
A001 99 2005-10-20 02:00:00.000
A001 81 2005-10-20 03:00:00.000
A001 62 2005-10-20 04:00:00.000
A001 43 2005-10-20 05:00:00.000
A001 25 2005-10-20 06:00:00.000
A001 6 2005-10-20 07:00:00.000
A001 88 2005-10-20 08:00:00.000
A001 69 2005-10-20 09:00:00.000
A001 25 2005-10-20 10:00:00.000
A001 32 2005-10-20 11:00:00.000
A001 13 2005-10-20 12:00:00.000
A001 94 2005-10-20 13:00:00.000
A001 76 2005-10-20 14:00:00.000
A001 57 2005-10-20 15:00:00.000
A001 39 2005-10-20 16:00:00.000
A001 12 2005-10-20 17:00:00.000
A001 1 2005-10-20 18:00:00.000
A001 83 2005-10-20 19:00:00.000
A001 64 2005-10-20 20:00:00.000
A001 45 2005-10-20 21:00:00.000
A001 27 2005-10-20 22:00:00.000
A001 8 2005-10-20 23:00:00.000