MySQL Выберите три последних отчета для отчета [дубликат]

Это решение является адаптацией из другого решения SO , спасибо RageZ за поиск этого связанного / аналогичного вопроса.

ПРИМЕЧАНИЕ

Это решение кажется удовлетворительным для использования Джастина. В зависимости от вашего варианта использования вы можете проверить решения Bill Karwin или David Andres в этой публикации. Решение Билла имеет мой голос! Посмотрите, почему, поскольку я поставил оба вопроса рядом друг с другом, -)

Преимущество моего решения состоит в том, что он возвращает одну запись для категории category_id (информация из таблицы элементов «свернута»). Основным недостатком моего решения является отсутствие читаемости и растущая сложность, так как количество желаемых строк растет (скажем, 6 строк для каждой категории, а не 6). Кроме того, это может быть немного медленнее по мере роста количества строк в таблице элементов. (Независимо от того, что все решения будут лучше работать с меньшим количеством допустимых строк в таблице элементов, и поэтому рекомендуется либо периодически удалять, либо перемещать старые элементы и / или вводить флаг, чтобы помочь SQL отфильтровывать строки раньше)

Первая попытка (не работает !!!) ...

Проблема с этим подходом заключалась в том, что подзапрос [по праву, но плохой для нас] производит очень много строк на основе декартовы произведения, определенные самим собой, соединяются ...

SELECT id, CategoryName(?), tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  here_some_addtional l criteria if needed
ORDER BY id ASC;

Вторая попытка. (работает нормально!) [/ ​​g8]

Предложение WHERE в добавлении для подзапроса, заставляя указанную дату быть последней, второй, последней и т. д. для i1, i2, i3 и т. д. соответственно (а также для нулевых случаев, когда для данного идентификатора категории имеется менее 4 элементов). Также добавлены не связанные элементы фильтра, чтобы предотвратить показ записей, которые «проданы», или записей, которые не имеют изображения (добавленные требования)

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

SELECT id, CategoryName, tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4, i4.date_listed
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed AND i2.sold = FALSE AND i2.image IS NOT NULL
          AND i1.sold = FALSE AND i1.image IS NOT NULL
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed AND i3.sold = FALSE AND i3.image IS NOT NULL
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed AND i4.sold = FALSE AND i4.image IS NOT NULL
    WHERE NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i1.date_listed)
      AND (i2.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i2.date_listed AND date_listed <> i1.date_listed)))
      AND (i3.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i3.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed)))
      AND (i4.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i4.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed AND date_listed <> i3.date_listed)))
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  --
ORDER BY id ASC;

Теперь ... сравните следующее, где я ввожу ключ item_id и использовать решение Билла, чтобы предоставить их список «внешнему» запросу. Вы можете понять, почему подход Билла лучше ...

SELECT id, CategoryName, image, date_listed, item_id
FROM item I
LEFT OUTER JOIN category C ON C.id = I.category_id
WHERE I.item_id IN 
(
SELECT i1.item_id
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id
      AND i1.sold = 'N' AND i2.sold = 'N'
      AND i1.image <> '' AND i2.image <> ''
      )
GROUP BY i1.item_id
HAVING COUNT(*) < 4
)
ORDER BY category_id, item_id DESC

ok после того, как быстрый поиск ответа был бы невозможен, по крайней мере, на mysql

этот поток для ссылки

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

В зависимости от того, насколько постоянны ваши категории, простейший маршрут

SELECT C.CategoryName, R.Image, R.date_listed
FROM
(
    SELECT CategoryId, Image, date_listed
    FROM 
    (
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Supplies'
      ORDER BY date_listed DESC LIMIT 4
    ) T

    UNION ALL

    SELECT CategoryId, Image, date_listed
    FROM
    (        
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Food'
      ORDER BY date_listed DESC LIMIT 4
    ) T
) RecentItemImages R
INNER JOIN Categories C ON C.CategoryId = R.CategoryId
ORDER BY C.CategoryName, R.Image, R.date_listed

В других базах данных вы можете сделать это с помощью функции ROW_NUMBER.

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        ROW_NUMBER() OVER (PARTITION BY category_id
                           ORDER BY date_listed DESC) AS rn
    FROM item
) AS T1
WHERE rn <= 4

К сожалению, MySQL не поддерживает функцию ROW_NUMBER, но вы можете эмулировать ее с помощью переменных:

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        @rn := IF(@prev = category_id, @rn + 1, 1) AS rn,
        @prev := category_id
    FROM item
    JOIN (SELECT @prev := NULL, @rn = 0) AS vars
    ORDER BY category_id, date_listed DESC
) AS T1
WHERE rn <= 4

Посмотрите, как он работает в сети: sqlfiddle

Он работает следующим образом:

• Внутри @prev установлено значение NULL и @rn устанавливается в 0.
• Для каждой строки, которую мы видим, проверьте, совпадает ли category_id с предыдущей строкой. Если да, увеличьте номер строки. В противном случае начните новую категорию и верните номер строки обратно в 1.
• . Когда подзапрос завершается, последний шаг - это фильтр, чтобы сохранить только строки с номером строки, меньшим или равным 4.

не очень красиво, но:

SELECT image 
FROM item 
WHERE date_listed IN (SELECT date_listed 
                      FROM item 
                      ORDER BY date_listed DESC LIMIT 4)

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

        declare @RowId int
 declare @CategoryId int
        declare @CategoryName varchar(MAX)

 create table PART (RowId int, CategoryId int, CategoryName varchar)
 create table  NEWESTFOUR(RowId int, CategoryId int, CategoryName varchar, Image image)
        select RowId = ROW_NUMBER(),CategoryId,CategoryName into PART from [Category Table]


        set @PartId = 0
 set @CategoryId = 0 
 while @Part_Id <= --count
 begin
   set @PartId = @PartId + 1
          SELECT @CategoryId = category_id, @CategoryName = category_name from PART where PartId = @Part_Id
          SELECT RowId = @PartId, image,CategoryId = @category_id, CategoryName = @category_name   FROM item into NEWESTFOUR where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

 end
 select * from NEWESTFOUR
 drop table NEWESTFOUR
        drop table PART