sql - 鉴于此模式设计，我如何选择每个主题的最高帖子并将它们关联起来？

Question

我正在为一个网站开发一个小型论坛组件，我正在创建一个页面，我想在其中显示每个主题及其评分最高的答案。表格如下所示:

POST       USER      TOPIC
id         id        id 
date       name      title   
text       bio       date
views                
likes
topic_id
author_id

我的查询是这样的:

select 
      u.id, u.name, u.bio, 
      p.id, p.date, p.text, p.views, p.likes, 
      t.id, t.title, t.date
 from
      ( select p.id, max(p.likes) as likes, p.topic_id 
        from post as p group by p.topic_id ) as q 

      inner join post as p on q.id = p.id
      inner join topic as t on t.id = q.topic_id 
      inner join user as u on u.id = p.author_id

 order by date desc;  

我在运行时遇到的问题之一是使用“q”。 Postgresql 不会让我运行“q”查询，因为它希望“p.id”位于“group by”子句或聚合函数中。我尝试使用“distinct on (p.id)”，但得到了相同的错误消息:p.id must appear in the GROUP BY clause or be used in an aggregate function.

没有 p.id 属性，我无法将它有意义地链接到其他表；有没有其他方法可以做到这一点？

Answer 1

;WITH cte AS (
    SELECT
       u.id AS UserId
       ,u.name
       ,u.bio
       ,p.id AS PostId
       ,p.[date] AS PostDate
       ,p.text
       ,p.views
       ,p.Likes
       ,t.id AS TopidId
       ,t.title
       ,t.[date] AS TopicDate
       ,p.Likes
       ,ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.Likes DESC, p.[date] DESC) AS RowNum
       ,DENSE_RANK() OVER (PARTITION BY t.id ORDER BY p.Likes DESC) AS RankNum
    FROM
       topic t
       INNER JOIN post p
       ON t.id = p.topic_id
       INNER JOIN [user] u
       ON p.author_id = u.id
)

SELECT *
FROM
    cte
WHERE
    RowNum = 1

;

如果您想查看最喜欢的关系，请将 RowNum 切换为 RankNum