想统计登录记录里面的各个IP登录次数,并且按登录次数排序表 aa.id a.ip a.logintime
1 152.1.3 ........
2 117.88.2.116 ........
3 127.0.0.1 ........
4 127.0.0.1 ........
5 117.88.2.116 ........
6 127.0.0.1 ........想要的结果ip 登录次数
127.0.0.1 3
117.88.2.116 2
152.1.3 1请高手赐教,非常感谢。
1 152.1.3 ........
2 117.88.2.116 ........
3 127.0.0.1 ........
4 127.0.0.1 ........
5 117.88.2.116 ........
6 127.0.0.1 ........想要的结果ip 登录次数
127.0.0.1 3
117.88.2.116 2
152.1.3 1请高手赐教,非常感谢。
select ip,count(*) as cnt
from a
group by ip
order by cnt desc
就是想要下面这样的ip 登录次数 最后登录时间
127.0.0.1 3 2011-11-2 8:30
117.88.2.116 2 。。
152.1.3 1 。。就是在原有基础上把每个IP的最后登录时间给取出来,非常感谢!这个属于我的疏忽,不管有没有回答我都会尽快结贴给分的,谢谢!
from a
group by ip
order by cnt desc
select ip,count(*),max(logintime) as cnt
from a
group by ip
order by cnt desc
select ip,count(*) as cnt,min(logintime) as lastlogin
from a
group by ip
order by cnt desc
select ip,登录次数=count(*),最后登录时间=max(logintime) from a group by ip
准确的是:
select ip,count(*) as cnt,max(logintime) as lastlogin
from a
group by ip
order by 2 desc
ip,count(1) as cnt,min(logintime) as lastlogin
from
a
group by
ip
order by
cnt desc