sql - 删除顺序重复项

Question

我有一个事件管理系统，每次更新记录时都会将行输入数据库

任务可能会开始 -> 编辑 -> 批准 -> 拒绝 -> 编辑 - 批准 -> 完成

然而，它可能会被某人更新几次，所以对于一个状态有多个条目，就像这样

开始->编辑->编辑->编辑->批准->拒绝->拒绝->编辑-批准->完成

我想删除连续的重复项，但如果稍后出现该状态则不会

我试过使用分区

SELECT taskid,version,TaskStatus, ROW_NUMBER() OVER (Partition by taskid,taskstatus ORDER BY taskid, cast(version as int)) As SetId 
into sqrank from sqtaskflow 

产生了

ID  VER     STATUS          RANK
16  1   QA Complete         1
16  2   QA Complete         2
16  3   QA Complete         3
16  4   In Queue            1
16  5   In Queue            2
16  6   In Queue            3
16  7   QA Complete         4
16  8   QA Complete         5
16  9   QA Complete         6
16  10  Task complete           1

期望的输出是

ID  VER     STATUS          RANK
16  1   QA Complete         1
16  4   In Queue            1
16  7   QA Complete         4
16  10  Task complete           1

Answer 1

假设 version 始终是连续的，没有间隙...

SELECT
  taskid,
  MIN(version)   AS first_version,
  TaskStatus,
  COUNT(*)       AS repeats
FROM
(
  SELECT
    taskid,
    CAST(version AS INT)  AS version,
    TaskStatus,
    ROW_NUMBER() OVER (Partition by taskid             ORDER BY taskid, cast(version as int)) AS task_ordinal,
    ROW_NUMBER() OVER (Partition by taskid, taskstatus ORDER BY taskid, cast(version as int)) AS task_status_ordinal
  FROM
    sqtaskflow
)
  AS sequenced
GROUP BY
  taskid,
  TaskStatus,
  task_ordinal - task_status_ordinal
ORDER BY
  taskid,
  TaskStatus,
  MIN(version)

示例数据

ID  VER   STATUS          RANK   Ver-Rank   Group

16  1     QA Complete     1      0          16,QA Complete,0
16  2     QA Complete     2      0          16,QA Complete,0
16  3     QA Complete     3      0          16,QA Complete,0

16  4     In Queue        1      3          16,In Queue,3
16  5     In Queue        2      3          16,In Queue,3
16  6     In Queue        3      3          16,In Queue,3

16  7     QA Complete     4      3          16,QA Complete,3
16  8     QA Complete     5      3          16,QA Complete,3
16  9     QA Complete     6      3          16,QA Complete,3

16  10    Task complete   1      9          16,Task Complete,9

编辑:

修改查询以创建用于代替 version 的顺序值(无间隙)。