使用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

谢谢。

- In0cybe

Answer 1

在 MySQL 8.0 之前，您不能使用窗口函数，如 ROW_NUMBER。但是您可以使用用户定义的变量来模拟 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;

在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;

在dbfiddle.uk上的演示

- Sebastian Brosch

---

如果您的 partition by 有两列呢？ :) - Roelant

我必须改变MySQL 5.7脚本版本中if()语句的顺序，以使行号按预期工作。顺便说一下，非常感谢！ - hieunt89

Answer 2

使用MySQL 5.7本地变量赋值实现PARTITION BY子句的替代方法是使用JSON，具体如下：

SELECT
  *,
  json_extract(
    @rn := json_set(
      @rn, @path := concat('$."', name, '"'), 
      (coalesce(json_extract(@rn, @path), 0) + 1)
    ), 
    @path
  ) AS rn
FROM table, (SELECT @rn := '{}') r
ORDER BY year DESC, month DESC;

这种方法的好处是，PARTITION BY子句不会影响整个查询的排序。

- Lukas Eder