Question

I have a table called comments, with the columns: id, title, comment, reply_to,create_date. Its a PostgreSQL DB, version 8.3. The column reply_to references the comment that is being is replied to.
I want to get comments with all their replies.

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

The comment with id 7 has been replied to by the comments with id 8, 9 and 10. To get that, I used this query:

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

That works fine, but now I would like to get only the last two replies for every comment, so the result should be:

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

我在问询中使用<条码>限制/代码>和<条码>offset,但如果我将其放在问询的最后部分,则只听取所有意见中的2份。如果我把他们放在问答中,则只收到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;

Major points

use window function row_number() to get the latest 2 postings
retrieves the columns you listed (for unknown reasons) plus a maximum of two replies
Includes comments with no replies at all (reply-fields are NULL then)

Major points

友情链接