sql - 有没有办法确保 WHERE 子句发生在 DISTINCT 之后？

Question

假设您的数据库中有一个表 comments。

评论表有列，id，text，show，comment_id_no。

如果用户输入评论，它会在数据库中插入一行

| id |  comment_id_no | text | show | inserted_at |
| -- | -------------- | ---- | ---- | ----------- |
| 1  | 1              | hi   | true | 1/1/2000    |

如果用户想要更新该评论，它会在数据库中插入一个新行

| id |  comment_id_no | text | show | inserted_at |
| -- | -------------- | ---- | ---- | ----------- |
| 1  | 1              | hi   | true | 1/1/2000    |
| 2  | 1              | hey  | true | 1/1/2001    |

注意它保持相同的 comment_id_no。这样我们就可以看到评论的历史记录。

现在用户决定不再显示他们的评论

| id |  comment_id_no | text | show  | inserted_at |
| -- | -------------- | ---- | ----- | ----------- |
| 1  | 1              | hi   | true  | 1/1/2000    |
| 2  | 1              | hey  | true  | 1/1/2001    |
| 3  | 1              | hey  | false | 1/1/2002    |

这对最终用户隐藏了评论。

现在有第二条评论(不是第一条的更新)

| id |  comment_id_no | text | show  | inserted_at |
| -- | -------------- | ---- | ----- | ----------- |
| 1  | 1              | hi   | true  | 1/1/2000    |
| 2  | 1              | hey  | true  | 1/1/2001    |
| 3  | 1              | hey  | false | 1/1/2002    |
| 4  | 2              | new  | true  | 1/1/2003    |

我希望能够做的是选择唯一 commend_id_no 的所有最新版本，其中 show 等于 true。但是，我不希望查询返回 id=2。

查询需要采取的步骤...

1. 选择所有最近的、不同的 comment_id_no。 (应返回 id=3 和 id=4)
2. 选择 where show = true(应该只返回 id=4)

Note: I am actually writing this query in elixir using ecto and would like to be able to do this without using the subquery function. If anyone can answer this in sql I can convert the answer myself. If anyone knows how to answer this in elixir then also feel free to answer.

Answer 1

您可以在不使用子查询的情况下使用 LEFT JOIN 执行此操作:

SELECT  c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM    Comments AS c
        LEFT JOIN Comments AS c2
            ON c2.comment_id_no = c.comment_id_no
            AND c2.inserted_at > c.inserted_at
WHERE   c2.id IS NULL
AND     c.show = 'true';

我认为所有其他方法都需要某种子查询，这通常通过排名函数来完成:

SELECT  c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM    (   SELECT  c.id, 
                    c.comment_id_no, 
                    c.text, 
                    c.show, 
                    c.inserted_at,
                    ROW_NUMBER() OVER(PARTITION BY c.comment_id_no 
                                      ORDER BY c.inserted_at DESC) AS RowNumber
            FROM    Comments AS c
        ) AS c
WHERE   c.RowNumber = 1
AND     c.show = 'true';

因为你已经用 Postgresql 标记了，你也可以使用 DISTINCT ON ():

SELECT  *
FROM    (   SELECT  DISTINCT ON (c.comment_id_no) 
                    c.id, c.comment_id_no, c.text, c.show, c.inserted_at
            FROM    Comments AS c 
            ORDER By c.comment_id_no, inserted_at DESC
        ) x
WHERE   show = 'true';

Examples on DB<>Fiddle