获取评论和回复

Question

我有一个名为comments,的表，其中包含以下列：id、title、comment和reply_to,create_date。它是一个PostgreSQL DB，8.3版。列reply_to引用了被回复的注释。

我想得到他们所有回复的评论。

Id / title / comment / reply_to / create_date  / reply_id / reply_title / reply_comment / reply_reply_to  / reply_create_date

7 / hello  / this is john / 6 / 2011-1-2   / 8  / Re:hello / Hello John! / 7 / 2011-1-4

7 / hello  / this is john / 6 / 2011-1-2   / 9  / Re:hello / John, welcome! / 7 / 2011-1-2

7 / hello  / this is john / 6 / 2011-1-2   / 10  / Re:hello / Nice to meet you, John / 7 / 2011-1-1

id为7的评论已经被id为8、9和10的评论回复了。

Select comments.id,comments.title
      ,comments.comment
      ,comments.reply_to
      ,comments.create_date
      ,B.id as reply_id
      ,B.title as reply_title
      ,B.comment as reply_comment
      ,B.reply to as reply_reply_to
      ,B. create_date as reply_create_date
  from comments 
  left join (select * from comments) B ON comments.id=B.reply_to
 order by create_date, reply_create_date DESC

这很好用，但现在我希望每个评论只得到最后两个回复，所以结果应该是：

7 / hello  / this is john / 6 / 2011-1-2   / 8  / Re:hello / Hello John! / 7 / 2011-1-4

7 / hello  / this is john / 6 / 2011-1-2   / 9  / Re:hello / John, welcome! / 7 / 2011-1-2

我认为在查询中使用limit和offset会有所帮助，但如果我将它们放在查询的最后部分，它只需要所有注释中的2个。如果我将它们放在B查询中，则只接受所有回复中的2个。

Answer 1

在PostgreSQL 9.0中，查询可能如下所示：

SELECT c.id
      ,c.title
      ,c.comment
      ,c.reply_to
      ,c.create_date
      ,b.reply_id
      ,b.reply_title
      ,b.reply_comment
      ,b.reply_reply_to
      ,b.reply_create_date
  FROM comments c
  LEFT JOIN (
    SELECT b.id          AS reply_id
          ,b.title       AS reply_title
          ,b.comment     AS reply_comment
          ,b.reply_to    AS reply_reply_to
          ,b.create_date AS reply_create_date
          ,row_number() OVER (PARTITION BY b.reply_to ORDER BY b.create_date DESC) AS rn
      FROM comments b
     WHERE rn < 3) b ON b.reply_to = c.id
 ORDER BY c.create_date, c.id, b.reply_create_date DESC;

要点