mysql in查询限制,MYSQL：每个whereIn（）限制行-CSDN博客

users Table

user_comments Table

id | user_id | content | created_at

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;

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.

** Edit: I cannot rely on the id column for ordering, it must use the date column.

Thanks.

** My Final Solution

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

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

mentioned by Bill Karwin.

Thanks everyone!

解决方案

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