mysql - 如何提取组的所有行？

这是我的桌子：

// notifications
+----+--------+-----------+---------+--------------------+
| id | money  | post_id   | user_id | belongs_to_user_id |
+----+--------+-----------+---------+--------------------+
| 1  | 5      | 1         | 123     | 101                |
| 2  | 10     | 2         | 123     | 101                |
| 3  | -2     | 4         | 456     | 101                |
| 5  | -2     | 2         | 456     | 101                |
| 6  | -2     | 3         | 123     | 101                |
| 7  | 5      | 4         | 789     | 101                |
| 8  | 10     | 4         | 789     | 101                |
+----+--------+-----------+---------+--------------------+

然后使用此查询选择一组分组的行：（最后三组）*

SELECT * FROM notifications
WHERE belongs_to_user_id = 101
GROUP BY post_id, user_id
ORDER BY id DESC
LIMIT 3

这是当前结果：

+----+--------+-----------+---------+--------------------+
| 5  | -2     | 2         | 456     | 101                |
| 6  | -2     | 3         | 123     | 101                |
| 7  | 5      | 4         | 789     | 101                | -- the eighth row is grouped with this
+----+--------+-----------+---------+--------------------+

虽然预期结果是一组这样的提取组：

+----+--------+-----------+---------+--------------------+
| 5  | -2     | 2         | 456     | 101                |
| 6  | -2     | 3         | 123     | 101                |
| 7  | 5      | 4         | 789     | 101                |
| 8  | 10     | 4         | 789     | 101                |
+----+--------+-----------+---------+--------------------+

我怎样才能做到这一点？

最佳答案

我相信你想要这个：

SELECT n.*
FROM notifications n JOIN
     (SELECT n2.post_id, n2.user_id
      FROM notifications n2
      WHERE n2.belongs_to_user_id = 101
      ORDER BY n2.id DESC
      LIMIT 3
     ) nn
     ON nn.post_id = n.post_id AND nn.user_id = n.user_id
ORDER BY id DESC;

问题中没有任何内容提示GROUP BY。您似乎对SQL的术语感到困惑。

关于mysql - 如何提取组的所有行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49669165/