复杂的SQL顺序

Question

id    |    message    |    reply id    |    date

1     |  my new app.. |     0          | 10 / 10 / 2009 (latest message on top, follow by replies)
5     | love ur app.. |     1          | 11 / 10 / 2009 (this should show under the main message)
6     | another comm  |     1          | 12 / 10 / 2009
2     | application 2 |     0          | 09 / 10 / 2009
3     | reply of 2    |     2          | 11 / 10 / 2009

我想在主要评论之后显示最新评论及其回复。显然，答复将具有最新日期，因此我无法按日期对日期进行排序，因为答复将位于主要日期之上。我不确定如何通过一个查询正确执行此操作。任何想法都可以。

数据库转储：http :
//pastie.org/576963

Answer 1

我猜文章的“回复ID”为0，评论的文章号为。如果这是您的设计，这应该可以工作：

select * from yourTable
order by
  case when "reply id" = 0 then id else "reply id" end, id

添加： 感谢您在评论中提供其他信息。将结果按所需顺序排列并非易事，因为第一个排序键是thread-
starter帖子的created_date。它不在数据行中，因此您需要一个联接。这是我根据其他信息得出的最佳猜测（该信息仍然不够完整，无法猜测）：

select
  f.id, f.user_id, f.type, f.reply_id, f.text, f.url, f.created_date,
  coalesce(parentfeed.created_date,f.created_date) as thread_date
from feed as f left outer join feed as parentfeed
on f.reply_id = parentfeed.id
order by
  thread_date desc,
  case when f.reply_id = 0 then 0 else 1 end,
  created_date desc, id;

您可能需要调整postgre的语法。我在SQL Server中对此进行了测试。

如果仍然不能满足您的要求，请具体说明如何恢复数据。最好，告诉我“ID”为了我应该看到在你的转储文件的数据， 也 说明该订单的基础。这是我所做的：

1. 线程中的所有消息（线程=一条消息及其注释）都应分组在一起。

2. 在一个线程中，将消息放在顶部，然后按相反的时间顺序排列其注释。具有最新创建/日期的线程应该位于第一个，然后具有第二创建日期最新的线程，依此类推。（您的样本数据有许多具有相同created_date的注释，因此我在线程中使用“ id”作为注释的第二顺序键。）

注意：
您的转储表明，如果帖子被修改，则created_date会更新为CURRENT_TIMESTAMP。如果这是一个实时留言板，请注意，这可能会导致注释在父消息
之前 过时，这意味着如果一个线程经常被修改（即使对其文本没有实际更改），它也将停留在最前面。（这与我的解决方案无关，但我认为这是值得注意的。）

因为需要联接，所以此查询现在会慢很多。我的建议：保留两个日期列，即“ thread_last_modified”和“
item_last_modified”。您将不得不将更新从线程启动器级联到注释级联，但是如果没有太多更新，那是值得的，因为查询可以简单得多。我尚未对此进行测试，因为它需要对您的设计进行几处更改：

select
  id, user_id, type, reply_id, text, url, thread_last_modified, item_last_modified
from feed
order by
  thread_last_modified desc,
  case when f.reply_id = 0 then 0 else 1 end,
  item_last_modified desc, id;

添加＃2 ：如果只希望包含ID为:: thisOne的注释的线程，我认为您可以在ON和ORDER
BY子句之间添加此行（对于我的第一个添加的解决方案，是join）：

where parentfeed.id = (
  select coalesce(reply_id,id)
  from feed
  where id = ::thisOne
)

从理论上讲，该查询只应对查询进行一次评估，但如果实际中不可行，则可以将其预先计算为:: thisOneThreadID并添加

where parentfeed.id = ::thisOneThreadID

对于第二种解决方案，假设您再次进行预计算，请尝试

where coalesce(id,reply_id) = ::thisOneThreadID

顺便说一句，我怀疑我的两个解决方案都将在同一时间合并上次修改的线程…