sql - 使用自连接根据列值从表中删除行

Question

我有一个要求，我的数据如下所示。我必须从表中找到最后一个 pid 状态未被“删除”的 ID。

注意:-1. 要获取最后的 pid 状态，请使用“日期”和“小时”列。2. 如果对于“id”，删除了 pid 的最后一个“status”值，则不要在结果中包含该行。

id  |   key    |    date     |  hour    | pid  | status
--------------------------------------------------------
id1 |   one    |    20180618 |  2       |  p1  | added
id1 |   one    |    20180618 |  3       |  p1  | removed
id1 |   one    |    20180618 |  4       |  p1  | added
id1 |   one    |    20180618 |  4       |  p2  | added

id1 |   one    |    20180619 |  2       |  p1  | removed
id1 |   one    |    20180619 |  4       |  p1  | added
id1 |   one    |    20180619 |  4       |  p2  | removed
id1 |   one    |    20180619 |  5       |  p3  | added

id2 |   one    |    20180619 |  5       |  p1  | added
id2 |   one    |    20180619 |  5       |  p2  | added
id2 |   one    |    20180619 |  6       |  p1  | removed

预期输出:-

id  |   key    |    date     |  hour    | pid  | status
--------------------------------------------------------
id1 |   one    |    20180619 |  4       |  p1  | added
id1 |   one    |    20180619 |  5       |  p3  | added
id2 |   one    |    20180619 |  5       |  p2  | added

我不想从源表中删除数据。我想使用自连接查询源表以产生上述结果。

Answer 1

使用 row_number() 函数来识别每个 id 和 pid 组合的最新记录，然后很容易只选择那些具有您想要的状态，如下所示:

declare @SampleData table (id varchar(32), [key] varchar(32), [date] date, [hour] int, pid varchar(32), [status] varchar(32));
insert @SampleData values
    ('id1', 'one', '20180618', 2, 'p1', 'added'),
    ('id1', 'one', '20180618', 3, 'p1', 'removed'),
    ('id1', 'one', '20180618', 4, 'p1', 'added'),
    ('id1', 'one', '20180618', 4, 'p2', 'added'),
    ('id1', 'one', '20180619', 2, 'p1', 'removed'),
    ('id1', 'one', '20180619', 4, 'p1', 'added'),
    ('id1', 'one', '20180619', 4, 'p2', 'removed'),
    ('id1', 'one', '20180619', 5, 'p3', 'added'),
    ('id2', 'one', '20180619', 5, 'p1', 'added'),
    ('id2', 'one', '20180619', 5, 'p2', 'added'),
    ('id2', 'one', '20180619', 6, 'p1', 'removed');

with OrderedDataCTE as
(
    select
        S.id, S.[key], S.[date], S.[hour], S.pid, S.[status],
        [sequence] = row_number() over (partition by S.id, S.pid order by S.[date] desc, S.[hour] desc)
    from
        @SampleData S
)
select
    O.id, O.[key], O.[date], O.[hour], O.pid, O.[status]
from
    OrderedDataCTE O
where
    O.[sequence] = 1 and
    O.[status] != 'removed';