Как получить последние 30 элементов из нескольких таблиц, упорядоченных по времени?

ProgramBox

Как получить последние 30 элементов из нескольких таблиц, упорядоченных по времени?

Post author:admin
Запись опубликована:26 апреля, 2023
Post category:Вопросы по программированию

#php #mysql #sql

Вопрос:

У меня есть примерно 3 таблицы для получения информации из них:

Статьи
Публикации
Фотографии

Во всех этих таблицах есть столбец ‘created_at’.

Я хочу получить последние 30 элементов, упорядоченных по столбцу created_at, с помощью SQL-запроса, в худшем случае мне придется извлечь 30 элементов из любой таблицы и упорядочить их из массива с помощью php-кода.

1. dev.mysql.com/doc/refman/5.0/en/join.html

2. Как JOIN могло бы помочь? Возможно, вы могли бы разработать какое-нибудь сложное перекрестное объединение, которое позволило бы вам идентифицировать 30 самых последних элементов во всех трех таблицах, но это вряд ли самый элегантный (или наиболее эффективный) подход. JOIN здесь совсем не тот инструмент.

3. в моих таблицах есть несколько идентичных полей, таких как (id, name, author_id), но есть и несколько неидентичных полей.

Ответ №1:

Как насчет чего-то подобного:

 (SELECT * FROM articles)
UNION 
(SELECT * FROM posts)
UNION 
(SELECT * FROM pictures)
ORDER BY created_at DESC 
LIMIT 30

Это непроверено, но должно сработать. Обязательно измените «*» на все используемые вами поля.

Ответ №2:

Основываясь на прошлых ответах, и добавьте сюда немного оптимизации:

 SELECT created_at, tbl, id, name, author_id FROM
  (SELECT created_at, "articles" as tbl, id, name, author_id 
   FROM `articles` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "posts" as tbl, id, name, author_id 
   FROM `posts` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "pictures" as tbl, id, name, author_id 
   FROM `pictures` ORDER BY created_at DESC LIMIT 30
  )
ORDER BY created_at DESC LIMIT 30;

FWIW — это непроверенный и может содержать глупые ошибки.

Сначала я собираюсь предположить, что created_at индексируется во всех трех таблицах. Я также собираюсь ограничить каждую таблицу возвратом только «последних 30 элементов», чтобы мы не делали заказ в потенциально огромной производной таблице (в которой мы не можем индексировать).

Таким образом, конечный «порядок по» в производной таблице гарантированно упорядочит <= 90 строк.
Намного лучше, чем заказывать полное объединение 3 таблиц.

Ответ №3:

Что-то вроде:

 SELECT * FROM
  (SELECT * FROM articles UNION SELECT * FROM posts UNION SELECT * FROM pictures)
ORDER BY created_at DESC LIMIT 30

Почти наверняка вам нужно будет заменить * список полей, общих для обеих таблиц, которые вы хотите выбрать (например, ID), и, возможно, что-то, чтобы определить, какой тип объекта это был (например, SELECT a, b, 'article' AS item_type )

Будьте осторожны, куда вы помещаете свои WHERE предложения. В идеале вы можете поместить по одному на каждый из внутренних выделений, чтобы ограничить, какие объекты туда включены, вместо того, чтобы помещать один массивный WHERE снаружи, который должен сначала дождаться выполнения всей UNION операции.

1. Для этого потребовалось бы, чтобы все 3 таблицы имели точно такие же столбцы, не так ли?

2. Я думаю, Lotus прав, если у меня есть несколько неидентичных полей, я не могу выполнить этот запрос.

3. Укажите столбцы, которые нужно включить, вместо использования *, и вы избежите этой проблемы.

4. Именно поэтому я сказал «вам нужно будет заменить * списком полей, общих для обеих таблиц, которые вы хотите выбрать». Не существует такой вещи, как запрос, который возвращает некоторые поля для одних строк и разные поля для других строк. Если вы надеетесь получить ВСЕ поля, единственный способ сделать это — SELECT *, NULL AS x, NULL AS y, NULL AS z заполнить все дополнительные поля из всех трех таблиц, так что вы получите кучу столбцов с большим количеством нулей. Как вы можете себе представить, это довольно некрасиво, поэтому лучше всего избегать этого, выбирая только то, что вам нужно.

Ответ №4:

Я бы выбрал:

 Select top 30 * from
(select acticles.created_at, ID 
from articles 
UNION
select posts.created_at, ID 
from posts 
UNION
select pictures.created_at, ID 
from pictures
ORDER BY 1)