sql-选择列顺序不同的记录

6pp0gazn  于 2021-07-29  发布在  Java
关注(0)|答案(3)|浏览(395)

给定下表,序列号和日期应该递增

+----+--------+------------+
| 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 |
+----+--------+------------+
smtd7mpg

smtd7mpg1#

可以使用窗口函数:

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
jpfvwuh4

jpfvwuh42#

你可以用 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;
uyhoqukh

uyhoqukh3#

您可以使用如下自连接(假设您的表名为“series”)获取有问题的行及其相应的冲突行:

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;

相关问题