sql - 在 T-SQL 中当另一行 <> 'X' 时检索具有最近日期的一行数据

Question

我有一个客户数据库，他们的成员(member)资格的生效日期和结束日期都是单独的列。但是，数据有点脏，一个客户可以有多行数据，其中只有一行是他们最近的成员(member)记录。如果成员的结束日期 = NULL，则将其视为“活跃”成员。

数据看起来有点像这样:

Name         ID          Membership_Effective_Date     Membership_End_Date
---------------------------------------------------------------------------
Bob           1                 1/1/2020                           NULL
Bob           1                 1/1/2017                           1/2/2017
Bob           1                 1/1/2017                           9/1/2018
Kim           2                 1/1/2019                           1/1/2020
Kim           2                 1/1/2019                           12/31/2019
Susan         3                 1/1/2018                           12/31/2018
Susan         3                 1/1/2019                           1/1/2019
Larry         4                 1/1/2020                           1/1/2020

我需要检索非事件和事件客户列表的最近成员资格结束日期。

我想要的结果应该是这样的:

Name ID Membership_Effective_Date Membership_End_Date
Bob 1 1/1/2020 NULL
Kim 2 1/1/2019 1/1/2020
Susan 3 1/1/2018 12/31/2018
Larry 4 1/1/2020 1/1/2020

对于具有Membership_End_Date 日期值的行和具有NULL 值的Membership_End_Date 行(Bob) 的客户以及具有仅具有日期值的多行的客户(Kim)，我已经能够毫无问题地执行此操作。

我面临的挑战是像 Susan 和 Larry 这样的数据。它们都有包含日期​​值的行，其中 Membership_Effective_Date = Membership_End_Date。在 Larry 的案例中，这是他拥有的唯一一行数据。在 Susan 的情况下，Membership_Effective_Date = Membership_End_Date 行中的日期大于另一行，因此我当前的查询将自动获取它。

问题是我基本上需要编写一个查询，说明如果客户有多行数据和一行，其中 Membership_Effective_Date = Membership_End_Date 然后选择第二个最近的数据行。但是，如果客户只有一行数据并且该行仅包含 Membership_Effective_Date = Membership_End_Date 的值，则选择该行。

如果不完全从数据拉取中删除 Larry，我无法弄清楚如何做到这一点，我需要包括他和类似的客户。

任何帮助表示赞赏!

Answer 1

你可以用 row_number() 做到这一点和条件排序:

select name, id, membership_effective_date, membership_end_date
from (
    select 
        t.*,
        row_number() over(
            partition by id 
            order by
                case when membership_end_date is null then 0 else 1 end,
                case when membership_end_date <> membership_effective_date then 0 else 1 end,
                membership_end_date desc
        ) rn
    from mytable t
) t
where rn = 1

诀窍在于 order by row_number()的条款: 它优先考虑结束日期为空的行，然后是结束日期不等于开始日期的行，然后是最大的结束日期。您可以单独运行子查询以查看行号是如何分配的。

有了这些信息，剩下要做的就是过滤每组排名最高的记录。

Demo on DB Fiddle :

姓名 |身份证 |成员(member)资格_生效日期|成员(member)资格_结束日期
:---- | -: | :------------------------ | :------------------
鲍勃 | 1 | 2020-01-01 |空值
金| 2 | 2019-01-01 | 2020-01-01
苏珊 | 3 | 2018-01-01 | 2018-12-31
拉里 | 4 | 2020-01-01 | 2020-01-01