mysql - 需要按类别选择5条最新记录？

Question

我需要选择每个品牌类别的五个最新产品，然后显示按类别分组的结果。

MySQL数据库的样子；

表产品:

product_id (pk) 
product_name 
product_etc 
product_publish_date 
brand_id (fk)

表品牌

brand_id (pk) 
brand_name

结果应该像下面这样...

brand_a product k
brand_a product a
brand_a product h
brand_a product l
brand_a product 33

brand_b product h
brand_b product z
brand_b product 9
brand_b product ddd
brand_b product a

尝试过

Answer 1

尝试:

SELECT p.*
FROM product p1
LEFT OUTER JOIN product p2
  ON (p1.brand_id = p2.brand_id AND p1.product_id < p2.product_id)
GROUP BY p1.item_id
HAVING COUNT(*) <= 5
ORDER BY brand_id, product_publish_date;

正如 bill karwin 所说，这与他的链接中的问题相同 - 我的回答只是根据您的情况改编该答案。