选择所有线程并按最新线程排序

一尘不染

选择所有线程并按最新线程排序

sql

现在，我获得了“选择所有论坛”并也获得了最新帖子。回答问题，我正在尝试编写查询以选择一个特定
论坛中的所有主题，并在最新帖子的日期（列“ updated_at”）之前对其进行排序。

这又是我的结构：

forums                      forum_threads              forum_posts
----------                  -------------             -----------
id                          id                        id
parent_forum (NULLABLE)     forum_id                  content
name                        user_id                   thread_id
description                 title                     user_id
icon                        views                     updated_at
                            created_at                created_at
                            updated_at
                            last_post_id (NULLABLE)

我尝试编写此查询，并且它可以正常工作..但是却没有达到预期：它没有按线程的最后发布日期对其进行排序：

SELECT DISTINCT ON(t.id) t.id, u.username, p.updated_at, t.title
FROM   forum_threads             t
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE t.forum_id = 3
ORDER  BY t.id, p.updated_at DESC;

我该如何解决？

阅读 136

2021-03-17

共1个答案

一尘不染

假设每个线程只需要一行，而不是所有帖子都需要所有行。

DISTINCT ON 仍然是最方便的工具。但是前导ORDER BY项必须与DISTINCT ON子句的表达式匹配。如果要以其他方式对结果进行排序，则需要将其包装到子查询中，然后将另一个添加ORDER BY到外部查询中：

SELECT *
FROM  (
   SELECT DISTINCT ON (t.id)
          t.id, u.username, p.updated_at, t.title
   FROM   forum_threads      t
   LEFT   JOIN forum_posts   p ON p.thread_id = t.id
   LEFT   JOIN users         u ON u.id = p.user_id
   WHERE  t.forum_id = 3
   ORDER  BY t.id, p.updated_at DESC
   ) sub
ORDER  BY updated_at DESC;

如果出于某种未知原因，您正在寻找 不带子 查询的查询，这也应该起作用：

SELECT DISTINCT
       t.id
     , first_value(u.username)   OVER w AS username
     , first_value(p.updated_at) OVER w AS updated_at
     , t.title
FROM   forum_threads      t
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE  t.forum_id = 3
WINDOW w AS (PARTITION BY t.id ORDER BY p.updated_at DESC)
ORDER  BY updated_at DESC;

这里发生了很多事情：

将表联接起来，并根据JOIN和WHERE子句选择行。
first_value()运行window函数的两个实例（在相同的窗口定义上）以从每个线程检索 最新帖子username并updated_at从 最新帖子中 检索。这导致与线程中的帖子一样多的相同行。
该DISTINCT步骤在窗口功能之后执行，并将每个集合简化为单个实例。
ORDER BY最后应用，并updated_at引用 OUT 列（SELECT列表），而不是同名的两 IN 列（FROM列表）之一。

另一个变体是 带有window函数row_number()的 子查询 ：

SELECT id, username, updated_at, title
FROM  (
   SELECT t.id
        , u.username
        , p.updated_at
        , t.title
        , row_number() OVER (PARTITION BY t.id
                             ORDER BY p.updated_at DESC) AS rn
   FROM   forum_threads      t
   LEFT   JOIN forum_posts   p ON p.thread_id = t.id
   LEFT   JOIN users         u ON u.id = p.user_id
   WHERE  t.forum_id = 3
   ) sub
WHERE  rn = 1
ORDER  BY updated_at DESC;

类似情况：

返回在一个列上不同但在另一列上排序的记录

您必须测试哪个更快。取决于几种情况。

2021-03-17