mysql分组查询前几条记录

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    category VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE
);

SELECT product_id, category, amount
FROM (
    SELECT product_id, category, amount,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) as rn
    FROM sales
) t
WHERE rn <= 2;

SELECT s.product_id, s.category, s.amount
FROM sales s
JOIN (
    SELECT category, GROUP_CONCAT(product_id ORDER BY amount DESC SEPARATOR ',') as top_products
    FROM (
        SELECT product_id, category, amount,
               @rank := IF(@cat = category, @rank + 1, 1) as rank,
               @cat := category
        FROM sales, (SELECT @rank := 0, @cat := '') r
        ORDER BY category, amount DESC
    ) t
    WHERE rank <= 2
    GROUP BY category
) t2 ON FIND_IN_SET(s.product_id, t2.top_products);