--测试数据 create table ipinfo(id int, ip varchar(20), date datetime) insert ipinfo select 1,'200.120.31.210','2005-7-14 7:10:22' union all select 2,'100.69.200.21' ,'2005-7-14 8:14:32' union all select 3,'210.31.200.14' ,'2005-7-15 9:00:14' --查询 declare @str varchar(8000), @i int select @i=0,@str='select 日期=convert(varchar(10), date, 120)' while @i<24 begin set @str=@str+' ,['+convert(varchar(2), @i)+']=sum(case when datepart(hh, date)>='+convert(varchar(2), @i)+ ' and datepart(hh, date)<'+convert(varchar(2), @i+1)+ ' then 1 else 0 end)' set @i=@i+1 end set @str=@str+' from ipinfo group by convert(varchar(10), date, 120)' --print @str exec(@str) --清除 drop table ipinfo
create table ipinfo(id int, ip varchar(20), date datetime)
insert ipinfo select 1,'200.120.31.210','2005-7-14 7:10:22'
union all select 2,'100.69.200.21' ,'2005-7-14 8:14:32'
union all select 3,'210.31.200.14' ,'2005-7-15 9:00:14'
--查询
declare @str varchar(8000), @i int
select @i=0,@str='select 日期=convert(varchar(10), date, 120)'
while @i<24
begin
set @str=@str+'
,['+convert(varchar(2), @i)+']=sum(case
when datepart(hh, date)>='+convert(varchar(2), @i)+
' and datepart(hh, date)<'+convert(varchar(2), @i+1)+
' then 1 else 0 end)'
set @i=@i+1
end
set @str=@str+'
from ipinfo
group by convert(varchar(10), date, 120)'
--print @str
exec(@str)
--清除
drop table ipinfo