如何用SQL统计下面的数据？

Question

--建表：
create table tst (
userid int null,
ip varchar(30),
Agent varchar(200),
time datetime
)
go--准备数据
insert tst
select 
NULL, '166.111.145.190', 'Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)', '2003-10-18 17:26:49'
union all
select
NULL, '166.111.145.190', 'Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)', '2003-10-18 17:26:57'
union all
select
NULL, '166.111.145.190', 'Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)', '2003-10-18 17:27:16'
union all
select
NULL, '172.16.17.123', 'Mozilla/4.0+(compatible;+MSIE+5.0;+Windows+98;+DigExt)', '2003-10-18 14:10:48'
union all
select
NULL, '172.16.2.30', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)', '2003-10-18 13:17:58'
union all
select
NULL, '172.16.3.109', 'Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)', '2003-10-18 13:29:32'
union all
select
NULL, '172.16.3.109', 'Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)', '2003-10-18 13:31:30'
union all
select
NULL, '172.16.3.109', 'Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)', '2003-10-18 13:31:35'
union all
select
NULL, '172.16.3.109', 'Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)', '2003-10-18 13:31:38'
union all
select
NULL, '172.16.3.109', 'Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)', '2003-10-18 13:31:55'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 12:37:42'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 12:37:53'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:02:18'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:02:36'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:03:01'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:03:36'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:04:26'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:06:12'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:06:58'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:07:07'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:17:32'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:17:46'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:18:09'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:18:13'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:18:25'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:18:47'
union all
select
NULL, '172.16.4.18', 'Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)', '2003-10-18 13:19:08'
go--更改
declare @i int
set @i=1
while exists (select * from tst where userid is null)
begin
set rowcount 1
update tst
set userid=@i
where userid is null
set rowcount 0 update b
set userid=a.userid
from tst b,tst a
where b.userid is null
and a.userid is not null
and a.ip=b.ip
and a.Agent=b.Agent
and a.userid=@i
and abs(datediff(minute,a.time,b.time))<30
and a.time=(select min(time) from tst 
where userid is not null
and ip=b.ip
and Agent=b.Agent
and userid=@i)
set @i=@i+1
end
go--查看结果
select * from tst
go

Answer 1

结果：
userid      ip                             Agent                                                                                                                                                                                                    time                                                   
----------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ 
1           166.111.145.190                Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)                                                                                                                                                      2003-10-18 17:26:49.000
1           166.111.145.190                Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)                                                                                                                                                      2003-10-18 17:26:57.000
1           166.111.145.190                Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)                                                                                                                                                      2003-10-18 17:27:16.000
2           172.16.17.123                  Mozilla/4.0+(compatible;+MSIE+5.0;+Windows+98;+DigExt)                                                                                                                                                   2003-10-18 14:10:48.000
3           172.16.2.30                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)                                                                                                                                                       2003-10-18 13:17:58.000
4           172.16.3.109                   Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)                                                                                                                                                 2003-10-18 13:29:32.000
4           172.16.3.109                   Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)                                                                                                                                                 2003-10-18 13:31:30.000
4           172.16.3.109                   Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)                                                                                                                                                 2003-10-18 13:31:35.000
4           172.16.3.109                   Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)                                                                                                                                                 2003-10-18 13:31:38.000
4           172.16.3.109                   Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MyIE+3.0)                                                                                                                                                 2003-10-18 13:31:55.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 12:37:42.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 12:37:53.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:02:18.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:02:36.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:03:01.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:03:36.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:04:26.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:06:12.000
5           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:06:58.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:07:07.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:17:32.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:17:46.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:18:09.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:18:13.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:18:25.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:18:47.000
6           172.16.4.18                    Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Q312461)                                                                                                                                                  2003-10-18 13:19:08.000（所影响的行数为 27 行）