时间戳

首页 > 解决方案 > 获取与历史上某个时间点的应用程序状态相对应的行

sql - 获取与历史上某个时间点的应用程序状态相对应的行

问题描述

我不确定如何用语言来描述我的问题，所以我创建了一个插图来提供帮助。

|-------------------------------------------|
| version_table (many-to-many)              |
|-------------------------------------------|
| version_id | a_id | b_id | operation_type |
|------------|------|------|----------------|
| 1          | 1    | 1    | INSERT         |
| 1          | 1    | 2    | INSERT         |
| 2          | 1    | 1    | DELETE         |
| 3          | 1    | 2    | DELETE         |
|------------|------|------|----------------|

在此表中查询每个版本会产生以下结果：

  1. 版本 1 应该返回两行（因为插入很明显）。
  2. 版本 2 应该返回一行（不太明显，但在调用 DELETE 操作之前该行存在）。
  3. 版本 3 应该返回零行（之前的 DELETE 操作清除的所有行）。

很明显，我们需要获取在提供的版本之前或之上插入的所有行。

WHERE table.version_id <= :VERSION

但不明显的是我们如何排除已“删除”的行。

AND table.version_id > alias.version_id AND alias.operation_type = "DELETE"

这是我最终写的查询：

SELECT tag.id AS tag_id, tag.name AS tag_name 
FROM tag
JOIN article_tag_version ON article_tag_version.tag_id = tag.id
LEFT OUTER JOIN article_tag_version AS article_tag_version_1 ON
    article_tag_version_1.tag_id = tag.id AND
    article_tag_version_1.operation_type = "DELETE"
WHERE article_tag_version.version_id <= ? AND article_tag_version.version_id > article_tag_version_1.version_id

...但它没有以我期望的方式返回结果（没有结果）。

标签： sqlpostgresql

解决方案

您可以检查NOT EXISTS“事物”（无论它是什么）是否已在它插入的版本和目标版本之间的版本中被删除。

SELECT *
       FROM version_table v1
       WHERE v1.operation_type = 'INSERT'
             AND NOT EXISTS (SELECT *
                                    FROM version_table v2
                                    WHERE v2.version_id >= v1.version_id
                                          AND v2.version_id <= :VERSION
                                          AND (v2.a_id,
                                               v2.b_id) = (v1.a_id,
                                                           v1.b_id)
                                          AND v2.operation_type = 'DELETE')
             AND v1.version_id <= :VERSION;

SQL小提琴

推荐阅读