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 ;
(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 ;
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
数据版本:5.6.20CREATE TABLE `t_log` (
`id` bigint(20) NOT NULL,
`userid` bigint(20) DEFAULT NULL,
`url` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `t_log_record` VALUES
(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/');想要得到每个用户访问次数最多的url的记录。如以上插入的测试数据中,想要得到的查询结果为:
+----+--------+---------------------------+
| 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>