如何加入不存在的行而不合并？

Question

我的表中有数据更改历史记录，看起来如下：

+--------+----------+---------------+
| id     | key      | value         |
+--------+----------+---------------+
| UUID-1 | editedBy | moderator     |
+--------+----------+---------------+
| UUID-1 | comments | deleted...    |
+--------+----------+---------------+
| UUID-2 | editedBy | low_moderator |
+--------+----------+---------------+
| UUID-2 | comments | deleted...    |
+--------+----------+---------------+
| UUID-3 | editedBy | admin         |
+--------+----------+---------------+
| UUID-4 | editedBy | admin2        |
+--------+----------+---------------+

现在我想用编辑器选择键值，用comments选择键值(这与deleted...不匹配)，所以我已经这样做了。

with table1 as 
(
   SELECT
      sch.rid,
      sch.rkey,
      sch.rvalue,
      coalesce( (
      SELECT
         rvalue 
      FROM
         test sch2 
      WHERE
         sch.rid = sch2.rid 
         AND sch2.rkey = 'comments' ), ' ' ) AS comments 
      FROM
         test sch 
      WHERE
         rkey = 'editedBy'
)
SELECT
   * 
FROM
   table1 t1 
WHERE
   t1.comments != 'deleted...'

我得到了想要的输出

+--------+----------+--------+----------+
| id     | key      | value  | comments |
+--------+----------+--------+----------+
| UUID-3 | editedBy | admin  |          |
+--------+----------+--------+----------+
| UUID-4 | editedBy | admin2 |          |
+--------+----------+--------+----------+

但是，在我看来，我的脚本太复杂了，有办法做得更好，但我无法单独找到它们。

Answer 1

窗口功能：

SELECT t.*
FROM (SELECT t.*,
             MAX(comments) OVER (PARTITION BY rid) as comments
      FROM test t
     ) t 
WHERE t1.comments <> 'deleted...'

Answer 2

您的描述表明，在具有相同UUID的行中只有两个键comments和editedBy，前者是可选的。

因此，简单地使用OUTER JOIN并过滤不想看到的行(带有注释的行)

select 
  ed.rid, ed.rkey, ed.rvalue
from table1 ed
left outer join table1 del
on ed.RID = del.RID and del.rkey = 'comments'
where ed.rkey = 'editedBy'
and del.rkey is NULL /* no comment */