MySQL在SELECT - WHERE IN语句中获取每个id的最后5个条目

Question

I would like to select last 5 entries for each of the id in a SELECT - WHERE IN statement.

//How to get last 5 entries for each id
SELECT * FROM table1
WHERE id IN (111,222,333,.....)
ORDER BY date DESC
LIMIT 5

EDIT: Example of how data structure

id  date    name
111 7-Nov   Anna
111 8-Nov   Belle
111 9-Nov   Mary
111 10-Nov  John
111 11-Nov  Robert
111 12-Nov  Gary
111 13-Nov  Rick
222 8-Apr   Sarah
222 9-Apr   Lee
222 10-Apr  Margaret
222 11-Apr  Lisa
222 12-Apr  Will
222 13-Apr  Alex
222 14-Apr  Kelly
222 15-Apr  Lucas

Desired results

id  date    name
111 13-Nov  Rick
111 12-Nov  Gary
111 11-Nov  Robert
111 10-Nov  John
111 9-Nov   Mary
222 15-Apr  Lucas
222 14-Apr  Kelly
222 13-Apr  Alex
222 12-Apr  Will
222 11-Apr  Lisa

I am not sure this can be done in mysql. I have tried looking at some answers posted and I can't find the answers.

Answer 1

It just pseudo code, but I think it works. try this

SELECT

id,

SUBSTRING_INDEX(GROUP_CONCAT( some_column_1 ORDER BY date DESC),',',5)

SUBSTRING_INDEX(GROUP_CONCAT( some_column_2 ORDER BY date DESC),',',5)

....

FROM table WHERE id IN (111,222,333,.....) GROUP BY id;

it look not like you want. I just showing how use GROUP_CONCAT and SUBSTRING_INDEX function, you apply this, you can find answer.