mysql - 按最大计数(字段)过滤结果

Question

这里是查询

SELECT `mpv`.`member_id` as member_id, `screen_name`, `viewed_url`, 
    count(viewed_url) as viewed_url_cnt
FROM (`tracker` mpv)
JOIN `members` m ON `mpv`.`member_id`=`m`.`member_id`
GROUP BY `viewed_url`, `member_id`

返回

+-----------+-------------+------------+----------------+
| member_id | screen_name | viewed_url | viewed_url_cnt |
+-----------+-------------+------------+----------------+
|         1 | admin       | /          |              1 |
|         1 | admin       | /1         |              1 |
|         1 | admin       | /2         |              1 |
|         1 | admin       | 2          |              1 |
|         1 | admin       | 3          |              2 |
|         2 | test        | 4          |              1 |
+-----------+-------------+------------+----------------+
6 rows in set (0.12 sec)

我需要获取具有最高 viewed_url_cnt 的所有记录分组依据 viewed_url , member_id 。因此，根据顶部的结果，我希望得到

+-----------+-------------+------------+----------------+
| member_id | screen_name | viewed_url | viewed_url_cnt |
+-----------+-------------+------------+----------------+
|         1 | admin       | 3          |              2 |
|         2 | test        | 4          |              1 |
+-----------+-------------+------------+----------------+
6 rows in set (0.12 sec)

谢谢

Answer 1

试试这个:

select s1.member_id, s1.screen_name, s1.viewed_url, s1.viewed_url_cnt from (
  select t1.member_id, t1.screen_name, t1.viewed_url, count(*) viewed_url_cnt
  from tracker t1
  group by t1.member_id, t1.screen_name, t1.viewed_url
) as s1
join (
  select s2.member_id, max(viewed_url_cnt) as viewed_url_max from (
    select t1.member_id, count(*) viewed_url_cnt
    from tracker t1
    group by t1.member_id, t1.viewed_url
  ) as s2
  group by s2.member_id
) as s3
on s1.member_id = s3.member_id and s1.viewed_url_cnt = s3.viewed_url_max