mysql - 选择每个关系的最高记录

Question

我需要得到所有用户访问次数最多的站点

我有以下表格:

页面:

id link_page
1  link 1
2  link 2
3  Link 3

用户:

id name
1 Joseph
2 Cris
3 Byram

页面用户:

  user_id  page_id
     1         1
     1         1
     1         1
     2         1
     2         1
     2         2

应该获得以下内容:

user_id | most visited page id
 1              1
 2              1

到目前为止，我做了这样的事情:

SELECT count(pu.page_id) as counter, pu.user_id 
FROM page_user pu
INNER JOIN page p on p.id = pu.page_id
INNER JOIN users u on u.id = pu.user_id
GROUP BY pu.user_id, pu.page_id 
ORDER BY counter DESC

但我返回所有记录。

因为我可以获得所有用户中访问次数最多的网站吗？

来自智利的问候。

Answer 1

你可以试试这个:

SELECT
  t.user_id,
  t.page_id AS most_visited_page_id
FROM (
      SELECT 
        pu.user_id,
        pu.page_id,
        COUNT(*) AS visitCount
      FROM page_user pu
        INNER JOIN page p ON p.id = pu.page_id
        INNER JOIN user u ON u.id = pu.user_id
      GROUP BY pu.user_id, pu.page_id
      ORDER BY pu.user_id, visitCount DESC, rand() <-- In case of multiple
                          page with same count it will give random page or you can check 
                          it with most recent visit with date if any -->

    ) AS t
GROUP BY t.user_id

这是 sqlfiddle .