列表中的前一项按两列排序

Question

假设我们有一个包含DAY和NUMERO列的表。

可以有许多行具有相同的DAY值，但NUMERO始终是唯一的。

在按DAY，NUMERO排序的列表中，最有效的方法是什么？

我精确地说，我需要这个mysql，我不想添加特定的索引（这就是为什么我不简单地使用DAY和NUMERO的线性函数的原因）。

这是一个有序的测试用例：

DAY | NUMERO
1   | 11
1   | 12
1   | 15
4   | 7
4   | 9
4   | 14
5   | 8
6   | 10
6   | 19

我的请求必须这样做：

（1,11）=>无

（1,15）=>（1,12）

（4,7）=>（1,15）

（4,9）=>（4,7）

（4,14）=>（4,9）

编辑：

我目前最好的解决方案是有两个连续的查询：

select * from item where day=? and numero<? order by day desc, numero desc limit 1;
select * from item where day<? order by day desc, numero desc limit 1;

如果第一个查询给出了结果，我就不必运行第二个查询了。

类似的解决方案是使用联合，但mysql似乎不会授权具有多个列的联合。

对于一个看起来如此简单的问题来说，这两种解决方案看起来都太重了。。。

Answer 1

更新

这里有一个解决方案，它实际上给出了正确的结果！

 SELECT T1.day, 
       T1.numero, 
       COALESCE(MAX(T2.[day]), MAX(T3.[day])) AS prev_day,
       COALESCE(MAX(T2.numero), MAX(T3.numero)) AS prev_numero
FROM @table AS T1
LEFT JOIN @table AS T2 ON 
(
    T2.[day] = T1.[day] AND T2.numero < T1.numero
)
LEFT JOIN @table AS T3 ON  
(
    T3.[day] < T1.[day] 
    AND NOT EXISTS(
        SELECT * 
        FROM @table AS T4
        WHERE T4.[day] > T3.[day] AND T4.[day] < T1.[day]
    )
)
-- Add where clause like so to get specific values
-- WHERE T1.day = 4 AND T1.numero = 7
GROUP BY T1.day, T1.numero
ORDER BY T1.day, T1.numero

结果：

day         numero      prev_day    prev_numero
----------- ----------- ----------- -----------
1           11          NULL        NULL
1           12          1           11
1           15          1           12
4           7           1           15
4           9           4           7
4           11          4           9
5           8           4           11
6           10          5           8
6           19          6           10

Answer 2

如果您只基于单个对检索前一对，而不是像其他人所暗示的那样尝试整个表，那么您可以尝试以下简单的查询-

:day = 4
:numero = 9
SELECT day, numero
FROM table
WHERE (day = :day AND numero < :numero)
OR (day < :day)
ORDER BY day DESC, numero DESC
LIMIT 1

Answer 3

这是一个普通查询，避免了CTE、聚合或窗口函数。

-- (a Before b) := (a.day < b.day
--               OR (a.day = b.day AND a.numero < b.numero))
SELECT d1.day AS DAY
        , d1.numero AS numero
        , d0.day AS day0
        , d0.numero AS numero0
  FROM tmp.lutser d1
  LEFT JOIN tmp.lutser d0
        ON (d0.day < d1.day OR (d0.day = d1.day AND d0.numero < d1.numero ))
  WHERE NOT EXISTS (SELECT *
     FROM tmp.lutser d
     WHERE (dx.day < d1.day OR (dx.day = d1.day AND dx.numero < d1.numero ))
       AND (dx.day > d0.day OR (dx.day = d0.day AND dx.numero > d0.numero ))
     )
ORDER BY day,numero
        ;

作为参考，这是使用窗口函数的查询：

SELECT day
        , numero
        , lag(day) OVER (w1)
        , lag(numero) OVER (w1)
FROM tmp.lutser
WINDOW w1 AS (ORDER BY day, numero)
        ;

结果：

 day | numero | day0 | numero0 
-----+--------+------+---------
   1 |     11 |      |        
   1 |     12 |    1 |      11
   1 |     15 |    1 |      12
   4 |      7 |    1 |      15
   4 |      9 |    4 |       7
   4 |     11 |    4 |       9
   5 |      8 |    4 |      11
   6 |     10 |    5 |       8
   6 |     19 |    6 |      10
(9 rows)