sql - 按 dense_rank() 对行进行分组并遍历每个子组并比较该子组下一行中的另一列？

Question

我在 LINQPad 中尝试了以下操作:

create table users
(
    id int not null,
    startdate datetime not null,
    enddate datetime not null
)

go

insert into users(id, startdate, enddate) values(1, '01/01/2000', '01/02/2000')
insert into users(id, startdate, enddate) values(1, '01/03/2000', '01/04/2000')

insert into users(id, startdate, enddate) values(2, '01/01/2000', '01/02/2000')
insert into users(id, startdate, enddate) values(2, '01/03/2000', '01/04/2000')
insert into users(id, startdate, enddate) values(2, '01/05/2000', '01/06/2000')

insert into users(id, startdate, enddate) values(3, '01/01/2000', '01/02/2000')
insert into users(id, startdate, enddate) values(3, '01/03/2000', '01/04/2000')
insert into users(id, startdate, enddate) values(3, '01/06/2000', '01/07/2000')

insert into users(id, startdate, enddate) values(4, '01/01/2000', '01/02/2000')

go

select * from users 

go

// This query gave the result seen in the image

select id, startdate, enddate, rownum = dense_rank() over(partition by id order by enddate) from users

我想编写一个只返回 ID 1 和 2(而不是 3 和 4)的查询，因为:

• ID 1 - 多于 1 行且其 rownum 2 的开始日期为 1 天在其行号 1 的结束日期之前
• ID 2 - 多于 1 行并且它的 rownum n + 1 的开始日期比它的 rownum 的结束日期提前 1 天n
• ID 3 - 虽然有超过 1 行，但其第 3 行的开始日期是它的 rownum 2 的结束日期不是提前 1 天(而是 2 天)。因此，它是不合格
• ID 4 - 不超过 1 行。因此，它不是合格

你能告诉我如何得到这个结果吗？

Answer 1

可以使用窗口函数lag()恢复之前的enddate，然后在having子句中进行聚合和过滤:

select id
from (
    select 
        t.*,
        lag(enddate) over(partition by id order by enddate) lag_enddate
    from users t
) t
group by id 
having 
    count(*) > 1
    and max(case 
        when lag_enddate is null or startdate = dateadd(day, 1, lag_enddate) 
        then 0 else 1 
    end) = 0

Demo on DB Fiddle :

| id || -: ||  1 ||  2 |

---

In archaic versions of SQL Server, that do not support window functions, you can emulate lag() with a correlated subquery:

select id
from (
    select 
        t.*,
        (select max(enddate) from users t1 where t1.id = t.id and t1.enddate < t.enddate) lag_enddate
    from users t
) t
group by id 
having 
    count(*) > 1
    and max(case when lag_enddate is null or startdate = dateadd(day, 1, lag_enddate) then 0 else 1 end) = 0

Demo on DB Fiddle