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
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