MySQL не поддерживает предложение limit внутри подзапроса, как я могу это сделать?

У меня есть следующая таблица в MySQL 5.1.30:

CREATE TABLE  article (
  article_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  category_id int(10) unsigned NOT NULL,
  title varchar(100) NOT NULL,
  PRIMARY KEY (article_id)
);

С помощью этой информации:

1, 1, 'foo'
2, 1, 'bar'
3, 1, 'baz'
4, 1, 'quox'
5, 2, 'quonom'
6, 2, 'qox'

Мне нужно получить первые три статьи в каждой категории для всех категорий, в которых есть статьи. Что-то вроде этого:

1, 1, 'foo'
2, 1, 'bar'
3, 1, 'baz'
5, 2, 'quonom'
6, 2, 'qox'

Конечно, профсоюз будет работать:

select * from articles where category_id = 1 limit 3
union
select * from articles where category_id = 2 limit 3

Но в базе данных есть неизвестное количество категорий. Кроме того, порядок должен указываться столбцами is_sticky и published_date, которые я не использовал для упрощения.

Можно ли создать запрос, который извлекает эту информацию?

ОБНОВЛЕНИЕ: Я пробовал следующее, которое, казалось бы, работало, за исключением того, что MySQL не поддерживает предложение limit внутри подзапроса. Вы знаете способ имитации лимита?

select *
  from articles a
  where a.article_id in (select f.article_id
                      from articles f
                      where f.category_id = a.category_id
                      order by f.is_sticky, f.published_at
                      limit 3)

Спасибо

Ответы

Ответ 1

SELECT... LIMIT не поддерживается в подзапросах, я боюсь, поэтому пришло время разгадать манеру самообучения:

SELECT article.*
FROM article
JOIN (
    SELECT a0.category_id AS id, MIN(a2.article_id) AS lim
    FROM article AS a0
    LEFT JOIN article AS a1 ON a1.category_id=a0.category_id AND a1.article_id>a0.article_id
    LEFT JOIN article AS a2 ON a2.category_id=a1.category_id AND a2.article_id>a1.article_id
    GROUP BY id
) AS cat ON cat.id=article.category_id
WHERE article.article_id<=cat.lim OR cat.lim IS NULL
ORDER BY article_id;

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

Если ваше "верхнее 3" требование может измениться до "top n" в какой-то момент, это начинает становиться громоздким. В этом случае вам может потребоваться пересмотреть идею запроса списка отдельных категорий, а затем объединение запросов для каждой категории.

ETA: Заказ на две колонки: eek, новые требования!: -)

Это зависит от того, что вы имеете в виду: если вы только пытаетесь заказать окончательные результаты, вы можете столкнуться с этим в конце, без проблем. Но если вам нужно использовать этот заказ, чтобы выбрать, какие три статьи нужно выбрать, все намного сложнее.

Мы используем самосоединение с '< для воспроизведения эффекта "ORDER BY article_id". К сожалению, пока вы можете делать "ORDER BY a, b, вы не можете делать" (a, b) < (c, d)... и вы не можете выполнить MIN (a, b). Кроме того, вы фактически заказываете три столбца, issticky, publish и article_id, потому что вам нужно убедиться, что каждое значение для заказа уникально, чтобы избежать получения четырех или более строк.

Пока вы можете составить свое упорядоченное значение с помощью грубой цельной или строковой комбинации столбцов:

LEFT JOIN article AS a1
ON a1.category_id=a0.category_id
AND HEX(a1.issticky)+HEX(a1.published_at)+HEX(a1.article_id)>HEX(a0.issticky)+HEX(a0.published_at)+HEX(a0.article_id)

это становится неоправданно уродливым, и вычисления будут искажать любую вероятность использования индексов, чтобы сделать запрос эффективным. В этот момент вам лучше просто выполнять отдельные запросы для каждой категории.

Ответ 2

Вероятно, вы должны добавить другую таблицу, содержащую category_id и описание категорий. Затем вы можете запросить эту таблицу для списка идентификаторов категорий и использовать подзапрос или дополнительные запросы для получения статей с правильной сортировкой и ограничением. У меня нет времени, чтобы написать это сейчас, но кто-то, вероятно, будет (или я сделаю это в маловероятном случае, когда никто другой не ответит к моменту моего возвращения).

Ответ 3

Здесь я не горжусь (в MS SQL - не уверен, что он будет работать в MySQL)

select a2.article_id, a2.category_id, a2.title
from 
    (select distinct category_id
    from article) as a1
    inner join article a2 on a2.category_id = a1.category_id

where a2.article_id <= (
    select top 1 a4.article_id 
    from (
         select top 3 a3.article_id 
         from article a3 
         where a3.category_id = a1.category_id 
         order by a3.article_id asc
    ) a4 
    order by a4.article_id desc)

Это будет зависеть от MySQL, поддерживающего подзапросы таким образом. В основном он разрабатывает третий по величине article_id для каждой категории и объединяет все статьи, которые меньше или равны по каждой категории.

SELECT TOP n * должен работать так же, как SELECT * LIMIT n, надеюсь...