查找丢失和乱序的记录

debugcn 发表于 Dev

米哈尔·罗莎（Michal Rosa）

模式：

简化-列出ID号，其版本和状态的表格：

CREATE TABLE archive
    ([id] int, [version] int, [status] varchar(1));

INSERT INTO archive
    ([id], [version], [status])
VALUES
    (1, 1, 'A'),
    (1, 2, 'S'),
    (1, 3, 'T'),
    (1, 4, 'A'),
    (2, 2, 'T'),
    (2, 4, 'T'),
    (3, 1, 'A'),
    (3, 3, 'A');

问题：

一些记录缺少其完整的历史记录（版本）。所有ID均应以版本1开头，版本号应连续（与上述架构中的ID 2和3不同）。

所需的输出

所有ID的列表，显示其现有版本以及“跳过”的版本。根据以下示例，输出应如下所示：

id | ver | check
---+-----+------
  1|   1 |   1
  1|   2 |   2
  1|   3 |   3
  1|   4 |   4
  2| NULL|   1
  2|   2 |   2
  2| NULL|   3
  2|   4 |   4
  3|   1 |   1
  3| NULL|   2
  3|   3 |   3

到目前为止，我的努力：

该问题类似于此问题，但是没有已解决的固定“ Table2”。每条记录的版本号未知。

到目前为止，我已经提出了以下建议：

SELECT sub.id, sub.ver, sub.seq
FROM (
      SELECT CASE WHEN a.id IS NULL THEN b.id ELSE a.id END as 'id', b.version as 'ver', a.seq as 'seq'
      FROM (select *,
                   row_number() over (partition by id order by version asc) as seq
              from archive) a
      FULL OUTER JOIN archive b ON a.id=b.id AND a.seq=b.version) sub
ORDER BY sub.id, sub.ver, sub.seq

通过以下输出，我几乎可以到达那里：

任何帮助，将不胜感激。

骄傲的埃德

这可以通过使用 recursive cte.

with cte as (
  select 1 as ctr, id, max(version) version from archive group by id
  union all 
  select ctr + 1, id, version from cte
  where ctr < version
)
select t1.id, t2.version, ctr as [check] from cte t1
left join archive t2 on t2.id = t1.id and t1.ctr = t2.version
order by t1.id, t1.ctr;

参见dbfiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。