sql - 复杂的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

我想在主要评论之后显示最新评论及其回复。显然回复会有最新日期，所以我不能按日期排序，因为回复将在 main 之上。我不确定如何通过一个查询正确执行此操作。请有任何想法。

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

Answer 1

我猜文章的“reply id”是0，是评论的文章编号。如果那是您的设计，那么这应该可行:

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

已添加:感谢您在评论中提供更多信息。将结果按您想要的顺序放置并不是那么容易，因为第一个排序键是 thread-starter post 的 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 的线程应该排在第一位，然后是具有第二个最新 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;

ADDED #2:如果您只想要包含 id 为::thisOne 的评论的线程，我认为您可以在 ON 和 ORDER BY 子句之间添加这一行(对于我添加的第一个解决方案，连接):

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

理论上，这个查找应该只对查询求值一次，但如果在实践中不是这样，您可以将它预先计算为::thisOneThreadID 并添加

where parentfeed.id = ::thisOneThreadID

对于第二种方案，假设你再次预计算，试试

where coalesce(id,reply_id) = ::thisOneThreadID

顺便说一句，我怀疑我的两个解决方案都会合并最后一次修改的线程...