如何根据同一表数据的两列从表中删除行？

Question

我有一个带有属性更改历史记录的表，看起来如下：

| id     | attr     | value  | version |
|--------|----------|--------|---------|
| UUID-1 | name     | chuck  | 1       |
| UUID-1 | password | morris | 1       |
| UUID-1 | name     | chack  | 0       |
| UUID-2 | name     | aron   | 2       |
| UUID-2 | name     | alan   | 1       |
| UUID-2 | name     | agor   | 0       |

现在，我必须删除每一个'id‘的旧数据(带有MAX(版本)的行应该保留)，我想得到：

| id     | attr     | value  | version |
|--------|----------|--------|---------|
| UUID-1 | name     | chuck  | 1       |
| UUID-1 | password | morris | 1       |
| UUID-2 | name     | aron   | 2       |

所以，我决定我可以通过这样的结构来获得这个：

DELETE
FROM
    table1 pch
WHERE
    EXISTS (
    SELECT
        id, MAX(version) AS maxVer
    FROM
        table1 pch2
    WHERE
        pch.id = pch2.id AND pch.version != maxVer
    GROUP BY
        id)

或者类似的东西但是..。oracle sql不能使用“where/ with”子句中的别名。

有别的办法吗？

Answer 1

只需使用关联子查询而不是group by

DELETE FROM table1 pch
WHERE pch.version < (SELECT MAX(version) AS maxVer
                     FROM table1 pch2
                     WHERE pch.id = pch2.id 
                    );

Answer 2

有很多不同的变体。只是其中之一：

delete from t
where 
t.rowid in 
(
select v.rowid 
from (select
       t1.rowid, 
       row_number()over(partition by t1.id,t1.attr order by version desc) rn
      from t t1
    ) v
where rn>1
);

Answer 3

这是一个可能的解决方案。

delete from history where rowid in
  (select history.rowid from history join
    (select ID, ATTR, max(version) version from history group by ID, ATTR) t
      on history.ID = t.ID and history.ATTR = t.ATTR and history.VERSION <> t.version);

首先，我们找到了不应该删除的最大版本的行。

然后，我们选择那些具有相同的ID和ATTR，但不同的VERSION (“旧”行)的行的rowids。

最后，我们用绳子移除这些行。