Mysql获取目标行数±2行

Question

我有一个类似如下的My_Table：PK1 | PK2 | Value | Date_Changed

和一个查询，它本质上是这样的：

SELECT 
  PK1,
  PK2,
  Value,
  ROW_NUMBER() OVER ( ORDER BY Value desc, Date_Changed ASC) AS position
FROM My_Table
    where PK1 = 1;

然后，我从查询结果where PK2 = myValue中获得position。

使用该position，我返回到该查询，并获得position±2行的行。

我使用的是MySQL5.7，所以我必须这样做：

SET @rownum = 0;
SET @bingo = NULL;

SELECT p.PK2 AS PK2,
       p.Value,
       p_o.Position
FROM My_Table p
         JOIN (
    SELECT PK1,
           PK2,
           @rownum := @rownum + 1 AS Position,
           CASE
               WHEN PK2 = param_PK2
                   THEN @bingo := @rownum
               ELSE 0
               END                AS bbb
    FROM My_Table
    WHERE PK1 = param_PK1
    ORDER BY value DESC, Date_Changed ASC
) p_o
              ON p.PK1 = p_o.PK1
                  AND p.PK2 = p_o.PK2
                  AND @bingo IS NOT NULL
                  AND Position < @bingo + offset
                  AND Position > @bingo - offset
ORDER BY p_o.Position;

这是一个可怕的查询，如果频繁执行的话。

有没有办法让它更轻一些？

由于它是MySQL5.7，我尝试在value列中添加一个反转的值列，因为在DESC上跳过了索引，但没有太大帮助。

我也尝试过通过ROW_NUMBER窗口函数来实现，作为MySQL8的一个实验，但是效果不是很好……

在这种情况下，光标会有帮助吗？

Answer 1

在MySQL 8.0中，公共表表达式可能会派上用场：

with cte as (
    select t.*, row_number() over(order by value desc, date_changed desc) position
    from mytable t
)
select c.* 
from cte c
inner join cte c1 on c.rn between c1.rn - 2 and c1.rn + 2
where c1.pk1 = 1 and c1.pk2 = 'myvalue'

Answer 2

给定的

+-----+------------+
| id  | dte        |
+-----+------------+
| 846 | 2020-04-26 |
| 847 | 2020-04-27 |
| 848 | 2020-04-28 |
| 849 | 2020-04-29 |
| 850 | 2020-04-30 |
| 851 | 2020-05-01 |
| 852 | 2020-05-02 |
| 853 | 2020-05-03 |
| 854 | 2020-05-04 |
+-----+------------+
9 rows in set (0.002 sec)

select id,dte
from dates
where dte = '2020-05-01'
union
(select id,dte
from dates
where dte < '2020-05-01'
order by dte desc limit 2
)
union all
(select id,dte
from dates
where dte > '2020-05-01'
order by dte limit 2
)
order by dte;

+-----+------------+
| id  | dte        |
+-----+------------+
| 849 | 2020-04-29 |
| 850 | 2020-04-30 |
| 851 | 2020-05-01 |
| 852 | 2020-05-02 |
| 853 | 2020-05-03 |
+-----+------------+
5 rows in set (0.004 sec)