Setting the Accesscount field can be added to the accesscount on a specific time scale if the same IP access is required.
Copy Code code as follows:
Create Table Counter
(
Counterid int identity (1,1) not NULL,
IP varchar (20),
Accessdatetime datetime,
Accesscount int
)
The table is here only for demonstration purposes, so only the most basic fields are available
Now insert a few records into the table
INSERT INTO Counter
Select ' 127.0.0.1 ', GETDATE (), 1 UNION ALL
Select ' 127.0.0.2 ', GETDATE (), 1 UNION ALL
Select ' 127.0.0.3 ', GETDATE (), 1
1 According to the year to query, month as time unit
Normally, a simple grouping can be done.
Copy Code code as follows:
Select
CONVERT (varchar (7), accessdatetime,120) as Date,
SUM (accesscount) as [Count]
From
Counter
GROUP BY
CONVERT (varchar (7), accessdatetime,120)
A month that is not recorded after grouping like this is not displayed, as follows:
This is certainly not what we want, so we have to change a way of thinking to achieve, as follows:
Copy Code code as follows:
DECLARE @Year int
Set @Year =2009
Select
M as [Date],
Sum
Case when DATEPART (month,accessdatetime) =m
Then Accesscount else 0 end
) as [Count]
From
Counter C,
(
Select 1 m
UNION ALL SELECT 2
UNION ALL Select 3
UNION ALL Select 4
UNION ALL Select 5
UNION ALL Select 6
UNION ALL Select 7
UNION ALL Select 8
UNION ALL Select 9
UNION ALL Select 10
UNION ALL Select 11
UNION ALL Select 12
) AA
where
@Year =year (Accessdatetime)
GROUP BY
M
The results of the query are as follows:
2 According to the day to query, in hours. This is similar to the above, and the code is as follows:
Copy Code code as follows:
DECLARE @DateTime DateTime
Set @DateTime =getdate ()
Select
Right (100+a,2) + ':-> ' +right (100+b,2) + ': ' As Datespan,
Sum
Case when DATEPART (hour,accessdatetime) > =a
and datepart (hour,accessdatetime) <b
Then Accesscount else 0 end
) as [Count]
From Counter C,
(SELECT 0 a,1 b
UNION ALL Select 1,2
UNION ALL Select 2,3
UNION ALL Select 3,4
UNION ALL Select 4,5
UNION ALL Select 5,6
UNION ALL Select 6,7
UNION ALL Select 7,8
UNION ALL Select 8,9
UNION ALL Select 9,10
UNION ALL Select 10,11
UNION ALL Select 11,12
UNION ALL Select 12,13
UNION ALL Select 13,14
UNION ALL Select 14,15
UNION ALL Select 15,16
UNION ALL Select 16,17
UNION ALL Select 17,18
UNION ALL Select 18,19
UNION ALL Select 19,20
UNION ALL Select 20,21
UNION ALL Select 21,22
UNION ALL Select 22,23
UNION ALL Select 23,24
) AA
Where DateDiff (day, @DateTime, Accessdatetime) =0
Group by Right (100+a,2) + ':-> ' +right (100+b,2) + ': 00 '
The results of the query are shown below:
