表结构: time ip userAgent address
10:04:59.000 220.189.222.226 220.189.222.226Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1) 浙江省宁波市电信
10:03:32.000 121.231.160.248 121.231.160.248Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 江苏省常州市电信
09:38:13.000 59.33.24.126 59.33.24.126Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省中山市电信ADSL
09:36:54.000 60.176.151.45 60.176.151.45Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 浙江省杭州市电信ADSL
09:35:14.000 219.148.30.18 219.148.30.18Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 河北省石家庄市电信
09:31:06.000 123.153.94.195 123.153.94.195Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; ) 浙江省台州市网通
09:25:31.000 120.87.212.189 120.87.212.189Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省中山市网通
09:24:59.000 119.141.217.150 119.141.217.150Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; ) 广东省东莞市电信
09:17:23.000 124.200.52.80 124.200.52.80Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 北京市宽带通
09:09:40.000 125.211.45.45 125.211.45.45Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 黑龙江省哈尔滨市网通
09:05:10.000 122.6.224.2 122.6.224.2Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;) 山东省滨州市电信
08:59:40.000 122.194.28.208 122.194.28.208Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1;) 江苏省镇江市网通
08:40:40.000 125.126.242.27 125.126.242.27Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 浙江省台州市(路桥区)电信
08:30:47.000 124.74.10.210 124.74.10.210Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;) 上海市黄浦区/卢湾区电信
08:24:35.000 121.57.230.237 121.57.230.237Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; ) 内蒙古电信
08:08:48.000 121.56.127.138 121.56.127.138Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 内蒙古赤峰市电信
08:08:43.000 218.22.172.235 218.22.172.235Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 安徽省马鞍山市电信
06:08:07.000 123.117.72.182 123.117.72.182Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 360SE) 北京市网通ADSL
05:48:54.000 61.144.224.48 61.144.224.48Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省深圳市电信ADSL
03:50:47.000 58.217.132.42 58.217.132.42Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; ) 江苏省南京市(下关区)电信ADSL取出前10条,如果结果中有ip+userAgent相同的情况下,显示时间最大的(总是显示10条) 注:ip+userAgent相同看成同一个人,就是同一个人的只显示一条(时间最大的那条)
10:04:59.000 220.189.222.226 220.189.222.226Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1) 浙江省宁波市电信
10:03:32.000 121.231.160.248 121.231.160.248Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 江苏省常州市电信
09:38:13.000 59.33.24.126 59.33.24.126Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省中山市电信ADSL
09:36:54.000 60.176.151.45 60.176.151.45Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 浙江省杭州市电信ADSL
09:35:14.000 219.148.30.18 219.148.30.18Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 河北省石家庄市电信
09:31:06.000 123.153.94.195 123.153.94.195Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; ) 浙江省台州市网通
09:25:31.000 120.87.212.189 120.87.212.189Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省中山市网通
09:24:59.000 119.141.217.150 119.141.217.150Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; ) 广东省东莞市电信
09:17:23.000 124.200.52.80 124.200.52.80Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 北京市宽带通
09:09:40.000 125.211.45.45 125.211.45.45Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) 黑龙江省哈尔滨市网通
09:05:10.000 122.6.224.2 122.6.224.2Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;) 山东省滨州市电信
08:59:40.000 122.194.28.208 122.194.28.208Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1;) 江苏省镇江市网通
08:40:40.000 125.126.242.27 125.126.242.27Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 浙江省台州市(路桥区)电信
08:30:47.000 124.74.10.210 124.74.10.210Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;) 上海市黄浦区/卢湾区电信
08:24:35.000 121.57.230.237 121.57.230.237Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; ) 内蒙古电信
08:08:48.000 121.56.127.138 121.56.127.138Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 内蒙古赤峰市电信
08:08:43.000 218.22.172.235 218.22.172.235Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; ) 安徽省马鞍山市电信
06:08:07.000 123.117.72.182 123.117.72.182Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 360SE) 北京市网通ADSL
05:48:54.000 61.144.224.48 61.144.224.48Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 广东省深圳市电信ADSL
03:50:47.000 58.217.132.42 58.217.132.42Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; ) 江苏省南京市(下关区)电信ADSL取出前10条,如果结果中有ip+userAgent相同的情况下,显示时间最大的(总是显示10条) 注:ip+userAgent相同看成同一个人,就是同一个人的只显示一条(时间最大的那条)
解决方案 »
- 如何查询表中某条记录的插入,及更新时间?
- 我想问一个在客户端使用数据库的问题
- 请教sql如何按照汇总的结果来排序?
- 求存储过程的书啊
- sql新手问题,帮帮忙..谢谢先
- sql的复制除了要用到1433端口外还需要什么端口?
- 酒店客房管理系统数据库设计分析
- 如何实现数据同步问题!!!!???
- 各位大侠,在SQL Server2000怎么编写一个自动备份数据库的脚本呢?
- liliah请教 库设计:我现在有计算机表(Sb_id,..),UPS表(Sb_id,..),打印机表(SB_id,..)。另有维修记录表(SB_id,date,result)。它们是什么关系?
- 请教各位,查询后,分组,汇总,及算百分比
- mysql中查询语句问题
select top 10 * from tb a where not exists(select 1 from tb where ip+userAgent=a.ip+a.userAgent and [time]>a.[time])
select top 10 * from tb a where time=(select max(time) from tb where ip=a.ip and userAgent=a.userAgent)
where not exists(
select 1 from tb
where ip=t.ip
and userAgent=t.userAgent
and [time]>t.[time])
order by [time]
select * from
(
select *,cout=row_number() over (partition ip,userAgent order by [time] desc) from tb
) a
where cout<10
as
select top 10 * from tb t where not exists( select 1 from tb where ip=t.ip and userAgent=t.userAgent and [time]>t.[time]) order by [time]
as
select top 10 * from tb t where not exists(select 1 from tb where ip=a.ip and userAgent=a.userAgent and [time]>t.[time])