単一の SQL クエリでカテゴリごとに 4 つの最新アイテムを取得するにはどうすればよいですか?

単一のクエリを使用して各カテゴリの最新アイテムを選択します

category_id という名前のフィールドによって分類されたアイテムのデータベースで、タスクはカテゴリのリストを取得することです。各カテゴリには、最近リストされた 4 つのアイテムが含まれています。カテゴリごとにデータベースに個別にクエリを実行するのではなく、単一の 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;

このクエリは、LEFT OUTER JOIN を使用して、各アイテム (i1) を、同じカテゴリを持つ更新されたアイテムのセット (i2) と結合します。 COUNT(*) 各カテゴリの各アイテムの一致数をカウントするために使用されます。 HAVING 句は、一致する項目が 5 つ以上ある項目をフィルターで除外し、各カテゴリで最新の 4 つの項目のみが選択されるようにします。

MySQL ユーザー変数を使用したソリューション:

このソリューションは、MySQL のユーザー変数機能を利用してグループ番号と行番号を追跡します。

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;

このクエリでは、ユーザー定義変数 @g と @r を使用して現在のカテゴリと行番号を追跡し、各カテゴリの最初の 4 つの項目のみが選択されるようにします。

MySQL ウィンドウ関数を使用したソリューション (MySQL 8.0.3):

MySQL 8.0.3 では 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;

このクエリは、PARTITION BY category_id ORDER BY date_listed DESC 句を使用して結果セットをカテゴリごとに分割し、各パーティションの date_listed 列によって項目を降順に並べ替えます。次に、ROW_NUMBER() ウィンドウ関数は各パーティションに連続した行番号を割り当て、各カテゴリの最初の 4 つの項目を選択できるようにします。

以上が単一の SQL クエリでカテゴリごとに 4 つの最新アイテムを取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。