56,687
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from t_log;
+----+--------+---------------------------+
| id | userid | url |
+----+--------+---------------------------+
| 1 | 101 | http://www.baidu.com/ |
| 2 | 101 | http://www.baidu.com/ |
| 3 | 101 | http://www.google.com.hk/ |
| 4 | 201 | http://www.google.com.hk/ |
| 5 | 201 | http://www.baidu.com/ |
| 6 | 201 | http://www.google.com.hk/ |
| 7 | 201 | http://www.so.com/ |
+----+--------+---------------------------+
7 rows in set (0.00 sec)
mysql> select userid,url
-> from (
-> select userid,url,count(*) as k
-> from t_log
-> group by userid,url
-> order by k desc
-> ) v
-> group by userid;
+--------+---------------------------+
| userid | url |
+--------+---------------------------+
| 101 | http://www.baidu.com/ |
| 201 | http://www.google.com.hk/ |
+--------+---------------------------+
2 rows in set (0.08 sec)
mysql>
select t1.* from
(select userid,url,count(id)cnt from t_log group by userid,url) t1,
(select userid,max(cnt)max_cnt from (select userid,url,count(id) cnt from t_log group by userid,url)t1 group by userid) t2
where ifnull(t1.userid,1)=ifnull(t2.userid,1) and t1.cnt=t2.max_cnt ;