mysql - 用 MySQL 5.7 模拟 PARTITION OVER

Question

我在 MySql 5.7 中有一个表，其中的名称根据日期重复。我需要恢复每个名字的前 2 条记录。

例子:

 name         year      month   
 xxxx         2019        8 
 xxxx         2019        7 
 xxxx         2019        6 
 xxxx         2019        5 
 ....         ....        ..
 zzzz         2019        5
 zzzz         2019        4
 zzzz         2019        3
 zzzz         2019        2
 ....         ....        ..

预期结果:

  name         year      month
  xxxx         2019        8 
  xxxx         2019        7 
  zzzz         2019        5
  zzzz         2019        4
  other ...

我需要为每个名字检索前两条记录，使用日期子句这样做是无效的。

我尝试模拟的查询:

SELECT
 name, year, month
FROM (
SELECT
    *,
    row_number() OVER (PARTITION BY name ORDER BY year DESC, month DESC)
FROM
    table
) a
WHERE row_number <= 2

谢谢。

Answer 1

在 MySQL 8.0 之前，您不能使用像 ROW_NUMBER 这样的窗口函数。但是你可以使用 user-defined variables而是模拟 ROW_NUMBER 函数:

SELECT name, year, month FROM (
  SELECT *, IF(@prev <> name, @rn:=0,@rn), @prev:=name, @rn:=@rn+1 AS rn
  FROM example, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
  ORDER BY name ASC, year DESC, month DESC
) t WHERE rn <= 2;

从 MySQL 8.0 开始，使用 ROW_NUMBER 查询就简单多了。功能:

SELECT name, year, month FROM (
  SELECT name, year, month, 
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, month DESC) AS rn
  FROM example
) t WHERE rn <= 2;

demo on dbfiddle.uk

---

如果您的分区依据有两列怎么办？

使用带有自定义变量的 MySQL 5.7(没有 ROW_NUMBER):

-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
    SELECT *, IF(@prev <> name + year, @rn:=0,@rn), @prev:=name + year, @rn:=@rn+1 AS rn
    FROM example, (SELECT @rn:=0)rn, (SELECT @prev:='')prev
    ORDER BY name ASC, year DESC, month DESC
)t WHERE rn <= 2;

使用 MySQL 8.0 和 ROW_NUMBER:

-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
  SELECT name, year, month, ROW_NUMBER() OVER (PARTITION BY name, year ORDER BY year DESC, month DESC) AS rn
  FROM example
)t WHERE rn <= 2;

demo on dbfiddle.uk