MYSQL: ограничение строк на whereIn ()

Таблица пользователей

id

user_comments Таблица

id | user_id | содержание | created_at

У меня есть список идентификаторов пользователей, и я хочу получить последние 3 комментария для каждого идентификатора пользователя.

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

Это захватит последние 3 комментария от всех совпадающих идентификаторов, мне нужны последние 3 комментария для каждого идентификатора. Предпочтителен 1 запрос без союзов.

Я пробовал правильно присоединиться к столу, но, похоже, не могу это сделать правильно.

** Изменить: я не могу полагаться на столбец идентификатора для заказа, он должен использовать столбец даты.

Спасибо.

** Мое окончательное решение

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, сработал для меня (с оговоркой), однако в итоге я выбрал указанное выше решение для соединения, которое я сделал с использованием информации из этого потока: ссылка

упомянутый Биллом Карвином.

Спасибо всем!


mysql greatest-n-per-group where-in sql-limit right-join
person magnito    schedule 08.02.2017    source источник
comment
ВЫБРАТЬ * ИЗ user_comments ГДЕ user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4 ORDER BY created_at DESC LIMIT 3;   -  person Álvaro Touzón    schedule 09.02.2017
comment
У вас есть столбец AUTO_INCREMENT?   -  person Paul Spiegel    schedule 09.02.2017
comment
@PaulSpiegel - Да, столбцы id - auto_inc.   -  person magnito    schedule 09.02.2017
comment
@ ÁlvaroTouzón - в некоторых случаях у меня есть сотни идентификаторов пользователей, в идеале мне не пришлось бы динамически генерировать такой запрос.   -  person magnito    schedule 09.02.2017
comment
stackoverflow.com/questions/1442527/   -  person Bill Karwin    schedule 09.02.2017
comment
@BillKarwin - мне удалось заставить его работать с правильным порядком даты, используя этот поток. Спасибо!   -  person magnito    schedule 09.02.2017

Ответы (2)


arrow_upward
2
arrow_downward

Если вы можете использовать id вместо created_at, вы можете сравнить id с третьим по величине id на пользователя. Что вы можете найти в подзапросе с LIMIT 1 OFFSET 2. В случае, если у пользователя меньше 3 комментариев, используйте COALESCE (или IFNULL), чтобы выбрать все комментарии с 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 для заказа ..

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 комментариев, если 3-й и 4-й комментарии имеют одинаковую временную метку.

person Paul Spiegel    schedule 08.02.2017
comment
К сожалению, я не могу полагаться на столбец id для упорядочивания комментариев, поскольку в моем приложении они могут быть сохранены как черновики и опубликованы позже. - person magnito; 09.02.2017
comment
@magnito Это также работает с использованием метки времени, но прочтите примечание. Я также добавил недостающее предложение ORDER BY в подзапрос. - person Paul Spiegel; 09.02.2017
comment
Мне удалось заставить это в основном работать, однако в итоге я выбрал решение для соединения, предложенное в ветке, о которой упоминал Билл Карвин. У него нет такой же проблемы с повторяющимися датами, и он работает быстрее (в моей ситуации). - person magnito; 09.02.2017

arrow_upward
1
arrow_downward

Пытаться

select * 
from (
    select *, 
        @currentRank := if(@prevId = user_id, @currentRank, 0) + 1 as rank, 
        @prevId := user_id
    from user_comments
    order by user_id, created_at desc) as user_comments 
where rank <= 3

Внутренний запрос использует переменные SQL @, которые изменяют значение от строки к строке. Комментарии конкретного пользователя будут сгруппированы вместе из-за order by user_id. @currentRank переменная будет хранить ранг строки в определенной группе. @currentRank будет обнулен при запуске новой группы.

Результат оказывается оптимальным, так как СУБД должна выполнять итерацию по каждой записи user_comments таблицы только один раз. Однако внешнее предложение where будет выполнено позже.

person Ivan Gritsenko    schedule 08.02.2017