Bagaimana untuk Mendapatkan Empat Item Terbaru Setiap Kategori dengan Satu Pertanyaan SQL?

Pilih item terkini dalam setiap kategori menggunakan satu pertanyaan

Dalam pangkalan data item yang dikelaskan mengikut medan bernama category_id, tugasnya adalah untuk mendapatkan semula senarai kategori, setiap satu mengandungi empat item tersenarai terkini. Daripada menanyakan pangkalan data secara individu untuk setiap kategori, gunakan satu pertanyaan SQL untuk mengoptimumkan panggilan pangkalan data.

Penyelesaian menggunakan sambung luar:

Pertanyaan berikut menggunakan sambung luar untuk mengenal pasti dan mengecualikan item yang telah mengemas kini rakan sejawatan dalam kategori yang sama:

<code class="language-sql">SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.category_id, i1.item_id
HAVING COUNT(*) <= 4;</code>

Pertanyaan ini menggunakan LEFT OUTER JOIN untuk menyertai setiap item (i1) dengan set item yang dikemas kini (i2) yang mempunyai kategori yang sama. COUNT(*) Digunakan untuk mengira bilangan padanan bagi setiap item dalam setiap kategori. Klausa HAVING menapis item dengan lebih daripada empat padanan, memastikan bahawa hanya empat item terbaharu dalam setiap kategori dipilih.

Penyelesaian menggunakan pembolehubah pengguna MySQL:

Penyelesaian ini menggunakan ciri pembolehubah pengguna MySQL untuk menjejak nombor kumpulan dan baris:

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, date_listed DESC
) AS t
WHERE t.rownum <= 4;</code>

Dalam pertanyaan ini, pembolehubah yang ditentukan pengguna @g dan @r digunakan untuk menjejaki kategori semasa dan nombor baris, memastikan hanya empat item pertama dalam setiap kategori dipilih.

Penyelesaian menggunakan fungsi tetingkap MySQL (MySQL 8.0.3):

MySQL 8.0.3 memperkenalkan sokongan untuk fungsi tetingkap standard SQL, memberikan penyelesaian yang lebih ringkas dan cekap:

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY date_listed DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

Pertanyaan ini menggunakan klausa PARTITION BY category_id ORDER BY date_listed DESC untuk membahagikan hasil yang ditetapkan mengikut kategori dan mengisih item dalam tertib menurun mengikut lajur date_listed dalam setiap partition. Fungsi tetingkap ROW_NUMBER() kemudian memberikan nombor baris berturut-turut kepada setiap partition, membolehkan pemilihan empat item pertama bagi setiap kategori.

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Empat Item Terbaru Setiap Kategori dengan Satu Pertanyaan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!