Jak wybrać najnowsze cztery przedmioty w kategorii?

To rozwiązanie jest adaptacją z innego rozwiązania So, dziękuję RageZ za zlokalizowanie tego związanego / podobnego pytania.

Uwaga

Pierwsza próba (nie zadziałała!!!)...

Problem z tym podejściem polegał na tym, że subquery [słusznie, ale źle dla nas] produkowałyby bardzo wiele wierszy, opartych na iloczynach kartezjańskich zdefiniowanych przez jaźń...

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;

Druga próba. (Działa ok!)

Klauzula WHERE dodana dla zapytania podrzędnego, wymuszająca, aby podana data była najnowsza, druga najnowsza, najnowsza itd. dla i1, i2, I3 itp. odpowiednio (a także dopuszczając przypadki null, gdy dla danego ID kategorii jest mniej niż 4 pozycje). Dodano również niepowiązane klauzule filtrujące, aby uniemożliwić wyświetlanie wpisów, które są "sprzedane" lub wpisy, które nie mają obrazu (Dodano wymagania)

Ta logika zakłada, że nie ma duplikowanych wartości daty (dla danej category_id). W przeciwnym razie takie przypadki utworzyłyby zduplikowane wiersze. efektywnie to użycie podanej daty jest monotonicznie incremented primary key as defined / required in Bill ' s solution.

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;

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

W innych bazach danych można to zrobić za pomocą funkcji 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

Niestety MySQL nie obsługuje funkcji ROW_NUMBER, ale można ją emulować za pomocą zmiennych:

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

Zobacz to działa online: sqlfiddle

To działa w następujący sposób:

• początkowo @prev jest ustawione na NULL, a @ rn na 0.
• dla każdego wiersza, który widzimy, sprawdź, czy identyfikator category_id jest taki sam jak poprzedni wiersz.
  • jeśli tak, zwiększ wiersz numer.
  • w przeciwnym razie uruchom nową kategorię i zresetuj numer wiersza z powrotem do 1.
• po zakończeniu zapytania podrzędnego, ostatnim krokiem jest filtrowanie tak, aby pozostały tylko wiersze o numerze rzędu mniejszym lub równym 4.

Niezbyt ładne ale:

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

W zależności od tego, jak stałe są Twoje Kategorie, poniżej znajduje się najprostsza droga

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

Poniższy kod pokazuje sposób na zrobienie tego w pętli zdecydowanie wymaga dużo edycji, ale mam nadzieję, że pomoże.

        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

Ostatnio natknąłem się na podobną sytuację, wypróbowałem zapytanie, które działało dla mnie, które jest niezależne od bazy danych

SELECT i.* FROM Item AS i JOIN Category c ON i.category_id=c.id WHERE
(SELECT count(*) FROM Item i1 WHERE 
i1.category_id=i.category_id AND 
i1.date_listed>=i.date_listed) <=3 
ORDER BY category_id,date_listed DESC;

Jest to równoważne uruchomieniu 2 dla pętli i sprawdzeniu, czy elementy nowsze od tej są mniejsze niż 3

Ok po googlowaniu szybka odpowiedź czy nie jest to możliwe przynajmniej na mysql

This this thread for reference

Może powinieneś buforować wynik tego zapytania, jeśli boisz się upaść na serwer i chcesz, aby Kod działał lepiej