mysql - 返回对用户配置文件的最近访问。 Group-by-N 混淆

Question

我有一个 user_profile_view 表:

定义如下:

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | bigint(20) | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) | NO   | MUL | NULL    |                |
| viewer_id  | bigint(20) | NO   | MUL | NULL    |                |
| created_at | datetime   | NO   |     | NULL    |                |
| updated_at | datetime   | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

我正在尝试获取对用户个人资料的最近访问，但我只想显示访问者一次

像这样的简单查询将返回配置文件的最近访问:

 SELECT v.* FROM user_profile_view v 
 WHERE v.user_id = 2 AND v.viewer_id != 2
 ORDER BY created_at DESC LIMIT 0,20;

结果集:

+------+---------+-----------+---------------------+---------------------+
| id   | user_id | viewer_id | created_at          | updated_at          |
+------+---------+-----------+---------------------+---------------------+
| 1314 |       2 |         1 | 2011-11-20 18:50:35 | 2011-11-20 18:50:35 |
| 1311 |       2 |         1 | 2011-11-17 00:51:10 | 2011-11-17 00:51:10 |
| 1307 |       2 |         1 | 2011-11-16 19:28:51 | 2011-11-16 19:28:51 |
| 1301 |       2 |         1 | 2011-11-16 19:08:08 | 2011-11-16 19:08:08 |
| 1299 |       2 |         1 | 2011-11-16 17:46:47 | 2011-11-16 17:46:47 |
| 1295 |       2 |         1 | 2011-11-16 17:34:09 | 2011-11-16 17:34:09 |
| 1277 |       2 |         1 | 2011-11-16 16:54:11 | 2011-11-16 16:54:11 |
| 1270 |       2 |         1 | 2011-11-16 15:51:09 | 2011-11-16 15:51:09 |
| 1109 |       2 |         1 | 2011-11-01 20:56:39 | 2011-11-01 20:56:39 |
| 1108 |       2 |         1 | 2011-11-01 20:56:34 | 2011-11-01 20:56:34 |
| 1100 |       2 |         1 | 2011-11-01 18:48:06 | 2011-11-01 18:48:06 |
| 1098 |       2 |         1 | 2011-11-01 18:43:44 | 2011-11-01 18:43:44 |
| 1097 |       2 |         1 | 2011-11-01 18:43:34 | 2011-11-01 18:43:34 |
|  950 |       2 |         1 | 2011-10-05 20:07:37 | 2011-10-05 20:07:37 |
|  948 |       2 |         1 | 2011-10-05 19:39:03 | 2011-10-05 19:39:03 |
|  944 |       2 |         1 | 2011-10-05 19:12:02 | 2011-10-05 19:12:02 |
|  941 |       2 |         1 | 2011-10-05 19:06:35 | 2011-10-05 19:06:35 |
|  935 |       2 |         1 | 2011-10-05 18:40:17 | 2011-10-05 18:40:17 |
|  933 |       2 |         1 | 2011-10-05 18:18:31 | 2011-10-05 18:18:31 |
|  932 |       2 |         1 | 2011-10-05 18:14:08 | 2011-10-05 18:14:08 |
+------+---------+-----------+---------------------+---------------------+
20 rows in set (0.00 sec)

但是我只想返回访问者 一次 在他们上次访问日期之前订购的。因此，如果用户连续三次访问用户个人资料，我将在 user_profile_view_table 中有 3 条记录，但我只想返回他们最后一次访问的记录。

我需要为此使用 Group-by-N，但我被卡住了。

这是我目前想到的:

SELECT v.viewer_id FROM user_profile_view v 
WHERE v.viewer_id != 2 AND v.user_id = 2
AND NOT EXISTS (SELECT * FROM user_profile_view v2
                WHERE v2.viewer_id = v.viewer_id AND v2.created_at < v.created_at)
ORDER BY created_at DESC;

Answer 1

SELECT v.viewer_id, MAX(created_at) AS MaxCreated
    FROM user_profile
    WHERE viewer_id != 2
        AND v.user_id = 2
    ORDER BY MaxCreated
    GROUP BY v.viewer_id;