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

Answer 2

您可以使用lag():

select t.*
from (select t.*,
lag(id) over (order by series) as prev_id_series,
lag(id) over (order by date) as prev_id_date
from t
) t
where prev_id_series <> prev_id_date;

Answer 3

您可以像这样使用SELF JOIN获取有问题的行及其相应的冲突行(假设您的表被称为"系列"(：

SELECT s1.id AS row_id, s1.series AS row_series, s1.date AS row_date, 
s2.id AS conflict_id, s2.series AS conflict_series, s2.date AS conflict_date
FROM series AS s1
JOIN series AS s2
ON s1.series > s2.series AND s1.date < s2.date;