使用MySQL 5.7模拟分区 [英] Emulating PARTITION OVER with MySQL 5.7

问题描述

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

I have a table in MySql 5.7, where the names are repeated according to the date. I need to recover the first 2 records for each name.

示例:

 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 ...

我需要检索每个名称的前两个记录，用date子句检索是无效的.

I need to retrieve the first two records for each name, it is not valid to do so with date clauses.

我尝试模拟的查询:

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

谢谢.

推荐答案

您可以使用以下解决方案:

You can use the following solution:

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;

在dbfiddle.uk上的演示

这篇关于使用MySQL 5.7模拟分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！