MYSQL:每个whereIn()限制行 [英] MYSQL: Limiting rows per whereIn()

问题描述

用户表

id

user_comments表

user_comments Table

id | user_id |内容| created_at

我有一个用户ID列表，我想获取每个用户ID的最新3条评论.

I have a list of user IDs, and I want to grab the latest 3 comments for each user id.

SELECT * FROM user_comments WHERE user_id IN (1, 2, 3, 4, 5) 
ORDER BY created_at DESC
LIMIT 3;

这将从所有 个匹配ID中获取最后3条注释，我希望每个 ID中具有最后3条注释.首选1个没有工会的查询.

This will grab the last 3 comments from all matching IDs, I want the last 3 comments for each ID. 1 query without unions preferred.

我已经尝试过正确地将表本身连接起来，但是我似乎无法正确地将其连接起来.

I have tried right joining the table on itself but I cant seem to get it right.

** 我不能依赖id列进行订购，它必须使用日期列.

谢谢.

** 我的最终解决方案

SELECT user_comments.* FROM user_comments
LEFT OUTER JOIN user_comments user_comments_2
ON user_comments.post_id = user_comments_2.post_id 
    AND user_comments.id < user_comments_2.id    
where user_comments.post_id in (x,x,x) 
GROUP BY user_comments.id 
HAVING COUNT(*) < 3 
ORDER BY user_id, created_at

@PaulSpiegel提出的答案确实对我有用(带有警告)，但是最后我还是使用了上面的联接解决方案，该解决方案是使用该线程中的信息制成的:

The answer proposed by @PaulSpiegel did work for me (with caveat), however I ended up going with the above join solution that I made using info from this thread: link

比尔·卡尔文提到的

谢谢大家！

推荐答案

如果可以使用id代替created_at，则可以将id与每个用户的第三高id进行比较.您可以在使用LIMIT 1 OFFSET 2的子查询中找到该内容.如果用户的注释少于3条，请使用COALESCE(或IFNULL)选择所有带有id >= 0的注释.

If you can use id instead of created_at, you can compare the id with the 3rd highest id per user. Which you can find in a subquery with LIMIT 1 OFFSET 2. For the case that a user has less than 3 comments use COALESCE (or IFNULL) to select all comments with id >= 0.

SELECT * 
FROM user_comments c
WHERE user_id IN (1, 2, 3, 4, 5)
  AND id >= COALESCE((
    SELECT id
    FROM user_comments c1
    WHERE c1.user_id = c.user_id
    ORDER BY id DESC
    LIMIT 1
    OFFSET 2
), 0)
ORDER BY user_id, id DESC

如果您不能使用id进行订购..

If you can not use id for ordering..

SELECT * 
FROM user_comments c
WHERE user_id IN (1, 2, 3, 4, 5)
  AND created_at >= COALESCE((
    SELECT created_at
    FROM user_comments c1
    WHERE c1.user_id = c.user_id
    ORDER BY created_at DESC
    LIMIT 1
    OFFSET 2
), '1970-01-01 00:00:00')
ORDER BY user_id, created_at DESC

请注意，如果第3条和第4条注释具有相同的时间戳记，那么您可能会(尽管不太可能)获得3条以上的注释.

Note that you then might (though unlikely) get more than 3 comments, if the 3rd and the 4th comment have the same timestamp.

这篇关于MYSQL:每个whereIn()限制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！