問題描述

我有一個項(xiàng)目數(shù)據(jù)庫.每個項(xiàng)目都使用類別表中的類別 ID 進(jìn)行分類.我正在嘗試創(chuàng)建一個列出每個類別的頁面，并在每個類別下方顯示該類別中的 4 個最新項(xiàng)目.

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

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

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

SELECT id FROM category

然后迭代該數(shù)據(jù)并查詢每個類別的數(shù)據(jù)庫以獲取最新項(xiàng)目:

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 個查詢并獲取所有這些數(shù)據(jù).我有 33 個類別，所以我認(rèn)為這可能有助于減少對數(shù)據(jù)庫的調(diào)用次數(shù).

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 個電話不是什么大問題，我應(yīng)該用簡單的方法來做.

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;

我假設(shè) item 表的主鍵是 item_id，并且它是一個單調(diào)遞增的偽鍵.也就是說，item_id 中較大的值對應(yīng)于 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.

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

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.

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

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 標(biāo)準(zhǔn)窗口函數(shù)的支持.現(xiàn)在我們可以像其他 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;

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