mysql - 如何获得每个组的最小行和第二最小行

Question

我有一张 table

fruit      ex.Date      price
banana     01/08/2016   57
banana     05/08/2016   60
banana     12/07/2016   90
grape      03/08/2016   40
grape      06/08/2016   20
grape      08/07/2016   10
apple      25/07/2016   80
apple      18/06/2016   20

现在我需要 mytable 中的两个表第一张表(基于每个水果组的最小日期)

fruit   ex.Date   price
banana     12/07/2016   90
grape      08/07/2016   10
apple      18/06/2016   80

然后是第二张表(基于每组的下一个日期)

fruit   ex.Date   price
banana     01/08/2016   57
grape      03/08/2016   40
apple      25/07/2016   80

Answer 1

为了得到每组的最少行数:

SELECT 
myTable.*
FROM myTable
INNER JOIN 
(
    SELECT 
    fruit,
    MIN(`ex.Date`) min_date
    FROM myTable
    GROUP BY fruit
) AS t
ON myTable.fruit = t.fruit AND myTable.`ex.Date` = t.`ex.Date`

为了获得每个组的第二个最小行数:

SELECT 
myTable.*
FROM myTable
INNER JOIN 
(
    SELECT 
    S.fruit,
    MIN(S.`ex.Date`) min_date
    FROM myTable S
    WHERE `ex.Date` > (SELECT MIN(T.`ex.Date`) FROM myTable T WHERE S.fruit = T.fruit)
    GROUP BY S.fruit
) AS t
ON myTable.fruit = t.fruit AND myTable.`ex.Date` = t.`ex.Date`;

---

编辑:

要推广获取第 n 个最小值 行的解决方案:

SELECT 
*
FROM 
(
  SELECT 
  *,
  IF(fruit = @sameFruit, @fruitRank := @fruitRank + 1,
      IF(@sameFruit := fruit, @fruitRank := 1,@fruitRank := 1)
  ) AS fruitRankNumber
  FROM myTable
  CROSS JOIN (SELECT @sameFruit := '', @fruitRank := 1 ) var
  ORDER BY fruit, `ex.Date`
) AS rankedFruitTable
WHERE rankedFruitTable.fruitRankNumber = X

注意:将 X 替换为您的期望值。如果您想获得具有最小 ex.Date 的行，则将 X 替换为 1。要获得每个水果的第二个最小行，请将 X 替换为 2 等等。