mysql - SQL - 选择其列不遵循相同顺序的记录

Question

鉴于我们有下表，其中系列号和日期应该递增

+----+--------+------------+
| id | series |    date    |
+----+--------+------------+
|  1 |     10 | 2020-08-13 |
|  2 |      9 | 2020-08-02 |
|  3 |      8 | 2020-06-23 |
|  4 |      7 | 2020-06-08 |
|  5 |      6 | 2020-05-20 |
|  6 |      5 | 2020-05-05 |
|  7 |      4 | 2020-05-01 |
+----+--------+------------+

有没有办法检查是否有不遵循此模式的记录？例如第 2 行有更大的序列号，但它的日期在第 3 行之前

+----+--------+------------+
| id | series |    date    |
+----+--------+------------+
|  1 |     10 | 2020-08-13 |
|  2 |      9 | 2020-06-02 |
|  3 |      8 | 2020-07-23 |
|  4 |      7 | 2020-06-08 |
|  5 |      6 | 2020-05-20 |
|  6 |      5 | 2020-05-05 |
|  7 |      4 | 2020-05-01 |
+----+--------+------------+

Answer 1

您可以使用窗口函数:

select *
from (
    select t.*, lead(date) over(order by series) lead_date
    from mytable t
) t
where date > lead_date

或者:

select *
from (
    select t.*, lead(series) over(order by date) lead_series
    from mytable t
) t
where series > lead_series