MySQL 从每个类别中选择前 N 个最便宜的产品

Question

这是 mySQL 产品表

+--------+-------+-----+
|Product | Price | Cat |
+--------+-------+-----+
| iPhone | 1     | 32  |
| Samsung| 2     | 32  |
| Dell   | 1     | 21  |
| HP     | 2     | 21  |
| RedMi  | 3     | 32  |
| Acer   | 3     | 21  |
+--------+-------+-----+

所需结果每个类别中最便宜的前 2 名:

+--------+-------+-----+
|Product | Price | Cat |
+--------+-------+-----+
| iPhone | 1     | 32  |
| Samsung| 2     | 32  |
| Dell   | 1     | 21  |
| HP     | 2     | 21  |
+--------+-------+-----+

我尝试从产品组中选择*按猫订单按价格但它只返回第一个最便宜的价格产品。我需要前 2 个最便宜的。

Answer 1

您可以使用变量来获取每个类别的产品排名:

SET @rn := 0;
SET @cat := 0;
SELECT product, price, cat FROM (
  SELECT @rn := case 
    WHEN @cat = cat then @rn + 1 
    ELSE 1
  END AS rn, product, price, cat,
  @cat := cat  
  FROM products 
  ORDER BY cat, price 
) t
WHERE rn <= 2
ORDER BY cat, rn

请参阅demo .
对于 MySQL 8.0+，有 ROW_NUMBER() :

SELECT product, price, cat
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY cat ORDER BY price) rn  
  FROM products
) t 
WHERE rn <= 2
ORDER BY cat, rn

请参阅demo .
结果:

| product | price | cat |
| ------- | ----- | --- |
| Dell    | 1     | 21  |
| HP      | 2     | 21  |
| iPhone  | 1     | 32  |
| Samsung | 2     | 32  |