时间戳

首页 > 解决方案 > 按顺序和状态从表分区中选择

sql - 按顺序和状态从表分区中选择

问题描述

所以我有以下搜索表：

ID  CreatedOn    Closed On    ClosedByUserID
_________________________________________
7 | 22/12/2020 | NULL       |     NULL       -> pending
6 | 21/12/2020 | 22/12/2020 |     NULL       -> outdated
5 | 20/12/2020 | 21/12/2020 |     NULL       -> outdated
4 | 19/12/2020 | 20/12/2020 |      1         -> closed
3 | 18/12/2020 | 19/12/2020 |      1         -> closed
2 | 17/12/2020 | 18/12/2020 |     NULL       -> outdated
1 | 16/12/2020 | 17/12/2020 |     NULL       -> outdated

状态是 ClosedOn 和 ClosedByUserID 的组合：

我正在尝试选择所有记录并尝试过滤重复的过时记录并仅显示最新记录。我仍然希望看到多个已关闭的搜索一个接一个，并且只能有一个待处理。

从上面的示例中，预期的结果将是：

ID  CreatedOn    Closed On    ClosedByUserID
_________________________________________
7 | 22/12/2020 | NULL       |     NULL       -> pending
6 | 21/12/2020 | 22/12/2020 |     NULL       -> outdated
4 | 19/12/2020 | 20/12/2020 |      1         -> closed
3 | 18/12/2020 | 19/12/2020 |      1         -> closed
2 | 17/12/2020 | 18/12/2020 |     NULL       -> outdated

我尝试使用分区来选择状态为已关闭或状态为待处理或（状态已过时且 RN = 1）的行：


;WITH SearchesWithStatus
AS
(
    SELECT  *,
            CASE
                WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
            END as SearchStatus,
            ROW_NUMBER() OVER (PARTITION BY CASE
                WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
            END ORDER BY CreatedOn DESC) rn
            FROM    Searches
)


SELECT *
FROM    SearchesWithStatus
order by CreatedOn desc

但是结果如下所示，并且在关闭记录后 RN 没有被重置为 1

ID  CreatedOn    Closed On    ClosedByUserID    Status     RN
_______________________________________________________________
7 | 22/12/2020 | NULL       |     NULL        | pending  | 1
6 | 21/12/2020 | 22/12/2020 |     NULL        | outdated | 1
5 | 20/12/2020 | 21/12/2020 |     NULL        | outdated | 2
4 | 19/12/2020 | 20/12/2020 |      1          | closed   | 1
3 | 18/12/2020 | 19/12/2020 |      1          | closed   | 2
2 | 17/12/2020 | 18/12/2020 |     NULL        | outdated | 3
1 | 16/12/2020 | 17/12/2020 |     NULL        | outdated | 4

分区是解决这个问题的正确方法吗？如果是这样，我错过了什么？

** 更新为还有两个搜索相继关闭的场景，我仍然想显示

标签： sqlsql-server

解决方案

我正在尝试选择所有记录并尝试过滤重复的过时记录并仅显示最新记录。

这意味着下一条记录不具有'OUTDATED'当前记录何时具有该状态的状态。

使用lead()：

WITH SearchesWithStatus AS (
      SELECT s.*,
             (CASE WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                   WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                   WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
              END) as SearchStatus
      FROM Searches s
     )
SELECT ss.*
FROM (SELECT ss.*,
             LEAD(SearchStatus) OVER (ORDER BY id) as next_status
      FROM SearchesWithStatus ss
     ) ss
WHERE SearchStatus <> 'OUTDATED' OR
      next_status IS NULL OR
      next_status <> 'OUTDATED';

注意：我不确定“最新”是否指的是idor CreatedOn。上述用途id- 两者在您的示例数据中是等价的。

推荐阅读