SQL选择比较日期与上一行

从这些数据中,我如何仅显示日期“ END”大于下一个“开始”日期的名称。

例如不应显示Peter的第一行“结束”日期为2019-12-31,下一个“开始”日期为2020-01-01,所以2019 <2020。现在,对于约翰来说,第一行“结束”日期为2021-12-31,该日期大于下一行“开始”日期为2020-03-01。

╔═════════╦════════════╦════════════╗
║名称║开始║结束║
╠═════════╬════════════╬════════════╣
║彼得║2016-01-01║2019-12-31║
║彼得║2020-01-01║2020-12-31║
║约翰║2018-01-01║2021-12-31║
║约翰║2020-03-01║2022-03-01║
║玛丽║2018-02-01║2022-01-31║
║玛丽║2020-01-01║2022-01-01║
║查尔斯(Charles)║2019-07-01║2021-06-30║
║查尔斯(Charles)║2020-03-01║2022-03-01║
╚═════════牛皮════════════牛皮════════════╝

所以从这些数据中我只能得到

╔═════════╦════════════╦════════════╗
║名称║开始║结束║
╠═════════╬════════════╬════════════╣
║约翰║2018-01-01║2021-12-31║
║约翰║2020-03-01║2022-03-01║
║玛丽║2018-02-01║2022-01-31║
║玛丽║2020-01-01║2022-01-01║
║查尔斯(Charles)║2019-07-01║2021-06-30║
║查尔斯(Charles)║2020-03-01║2022-03-01║
╚═════════牛皮════════════牛皮════════════╝
评论
  • 99.90%
    99.90% 回复

    If you just want rows with overlaps, you can use exists:

    select t.*
    from t
    where exists (select 1
                  from t t2
                  where t2.name = t.name and
                        t2.end > t.start and
                        t2.start < t.start
                 );
    
  • ad_est
    ad_est 回复

    You want rows whose end date is greater than the next begin for the same name You can use window functions for this

    select t.name, tLbegin, t.end
    from (
        select
            t.*,
            lead(t.begin) over(partition by t.name order by t.begin) lead_begin
        from mytable t
    ) t
    where t.end > t.lead_begin or lead_begin is null
    

    Note that this would not remove the second record for 'John' (since it does not have a next record).

    Side note: begin and end are reserved words in SQL, hence not good choices for column names.