mysql - 在 MySQL 中获取不同的 ID

Question

我有一个这样的表结构:

Comments table
id      review_id    user_id  created     comments deleted
1       10           28       2011-10-12  "hi"     0
2       11           28       2011-10-13  "yo"     0
3       10           28       2011-10-15  "bye"    0

我想从按创建的 DESC 排序的每条评论中获取最新评论。所以我有这样的东西:

SELECT DISTINCT review_id, 'comments' as type FROM comments as MyTable WHERE 
user_id=28 AND deleted=0 ORDER BY created DESC LIMIT 30

因此在上表中，我希望返回 id = 3 的行，然后返回 id = 2。

Answer 1

示例查询:

https://data.stackexchange.com/stackoverflow/s/2143/getting-distinct-ids-in-mysql

SELECT id,comment
FROM comments
WHERE id IN (
    SELECT MAX(id)
    FROM comments
    WHERE user_id=28 AND deleted=0
    GROUP BY review_id )
ORDER BY created DESC