返回每个小时独立的IP数量

 

 

  If object_id ( ' ta ' ) is not null
    Drop table ta
Go
Create table ta(requestDate datetime ,requestIP varchar ( 11 ))
Go
Insert into ta
select ' 2007-11-1 00:00:00 ' , ' 192.168.0.1 ' union all
select ' 2007-11-1 00:00:00 ' , ' 192.168.0.2 ' union all
select ' 2007-11-1 01:00:00 ' , ' 192.168.0.2 ' union all
select ' 2007-11-1 01:00:00 ' , ' 192.168.0.3 ' union all
select ' 2007-11-1 02:00:00 ' , ' 192.168.0.3 ' union all
select ' 2007-11-1 02:00:00 ' , ' 192.168.0.4 ' union all
select ' 2007-11-1 02:00:00 ' , ' 192.168.0.5 ' union all
select ' 2007-11-1 03:00:00 ' , ' 192.168.0.3 ' union all
select ' 2007-11-1 03:00:00 ' , ' 192.168.0.5 ' union all
select ' 2007-11-1 03:00:00 ' , ' 192.168.0.6 '
Go
-- Start
select datepart (hh,requestDate), count ( 1 ) as ipcount
from ta a
where not exists ( select 1 from ta where requestIP = a.requestIP and datepart (hh,a.requestDate) > datepart (hh,requestDate))
group by datepart (hh,requestDate), convert ( char ( 10 ),requestDate, 120 )



-- Result:
/*
            ipcount    
----------- -----------
0           2
1           1
2           2
3           1

（所影响的行数为 4 行）

*/
-- End