問題描述

我有一個項目數據庫.每個項目都使用類別表中的類別 ID 進行分類.我正在嘗試創建一個列出每個類別的頁面，并在每個類別下方顯示該類別中的 4 個最新項目.

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category.

例如:

寵物用品

img1
img2
img3
img4

寵物食品

img1
img2
img3
img4

我知道我可以通過像這樣查詢每個類別的數據庫來輕松解決這個問題:

I know that I could easily solve this problem by querying the database for each category like so:

SELECT id FROM category

然后迭代該數據并查詢每個類別的數據庫以獲取最新項目:

Then iterating over that data and querying the database for each category to grab the newest items:

SELECT image FROM item where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

我想弄清楚的是，我是否可以只使用 1 個查詢并獲取所有這些數據.我有 33 個類別，所以我認為這可能有助于減少對數據庫的調用次數.

What I'm trying to figure out is if I can just use 1 query and grab all of that data. I have 33 categories so I thought perhaps it would help reduce the number of calls to the database.

有誰知道這可能嗎?或者如果 33 個電話不是什么大問題，我應該用簡單的方法來做.

Anyone know if this is possible? Or if 33 calls isn't that big a deal and I should just do it the easy way.

推薦答案

這是最大的 n-per-group 問題，也是一個非常常見的 SQL 問題.

This is the greatest-n-per-group problem, and it's a very common SQL question.

這是我使用外連接解決它的方法:

Here's how I solve it with outer joins:

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.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

我假設 item 表的主鍵是 item_id，并且它是一個單調遞增的偽鍵.也就是說，item_id 中較大的值對應于 item 中較新的行.

I'm assuming the primary key of the item table is item_id, and that it's a monotonically increasing pseudokey. That is, a greater value in item_id corresponds to a newer row in item.

這是它的工作原理:對于每個項目，都有一些其他較新的項目.例如，有比第四個最新項目更新的三個項目.零個項目比最新項目新.因此，我們希望將每個項目 (i1) 與較新且與 i1 具有相同類別的項目集 (i2) 進行比較.如果這些新項目的數量少于四個，i1 就是我們包含的項目之一.否則，請不要包含它.

Here's how it works: for each item, there are some number of other items that are newer. For example, there are three items newer than the fourth newest item. There are zero items newer than the very newest item. So we want to compare each item (i1) to the set of items (i2) that are newer and have the same category as i1. If the number of those newer items is less than four, i1 is one of those we include. Otherwise, don't include it.

此解決方案的美妙之處在于，無論您擁有多少個類別，它都能正常工作，并且在您更改類別時繼續工作.即使某些類別中的項目數少于四個，它也能正常工作.

The beauty of this solution is that it works no matter how many categories you have, and continues working if you change the categories. It also works even if the number of items in some categories is fewer than four.

另一種可行但依賴于 MySQL 用戶變量功能的解決方案:

Another solution that works but relies on the MySQL user-variables feature:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (@g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 3;

<小時>

MySQL 8.0.3 引入了對 SQL 標準窗口函數的支持.現在我們可以像其他 RDBMS 一樣解決這類問題:

MySQL 8.0.3 introduced support for SQL standard window functions. Now we can solve this sort of problem the way other RDBMS do:

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

這篇關于如何選擇每個類別的最新四個項目?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！