复杂的SQL顺序 [英] complex sql order by

问题描述

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

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

I want to show latest comments and its replies after the main comments. Obviously the replies will have latest date, so I can't sort it by date, because replies will be on top of main. I'm not sure how to do this properly with one query. Any idea's please.

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

推荐答案

我猜文章的回复ID"为0，是评论的文章号.如果这是您的设计，那么它应该可以工作:

I'll guess that "reply id" is 0 for articles and is the article number for comments. If that's your design, this should work:

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

已添加:感谢您评论中的其他信息.将结果按所需顺序排列并非易事，因为第一个排序键是thread-starter帖子的created_date.它不在数据行中，因此您需要一个联接.这是我根据其他信息(可能还不够完整，无法猜测)做出的最佳猜测:

ADDED: Thanks for the additional information in your comment. To put the results in the order you want is not so easy, because the first ordering key is the created_date of the thread-starter post. This is not in the data row, so you need a join. Here is my best guess based on the additional information (which is still not complete enough to keep me from guessing):

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中对此进行了测试.

You may need to adjust syntax for postgre. I tested this in SQL Server.

如果这仍然不能满足您的要求，请具体说明如何恢复数据.最好告诉我应该为您的转储文件中的数据看到的"id"顺序，并且还要解释该顺序的基础.这是我所做的:

If this still doesn't do what you want, please be specific about how you want the data back. Preferably, tell me the "id" order I should see for the data in your dump file, and also explain the basis for that order. Here's what I did:

1. 线程中的所有消息(线程=消息及其注释)都应分组在一起.

1. All messages in a thread (thread = a messages and its comments) should be grouped together.

在线程中，将消息放在顶部，然后按相反的时间顺序排列其注释.具有最新创建/日期的线程应该是第一个，然后具有第二创建日期最近的线程，以此类推. (您的样本数据中有许多具有相同created_date的注释，因此我使用"id"作为线程中注释的第二级关键字.)

Within a thread, put the message on top, followed by its comments in reverse chronological order. The thread with the most recent created/_date should be first, then the thread with the second most recent created_date, and so on. (Your sample data had many comments with the same created_date, so I used "id" as a secondary order key for the comments within a thread.)

注意: :您的转储表明，如果帖子被修改，那么created_date会更新为CURRENT_TIMESTAMP.如果这是一个实时留言板，请注意，这可能会导致注释在父消息之前之前标出日期，这意味着如果一个线程经常被修改(即使没有实际更改)，它也将停留在最前面.其文字). (这与我的解决方案无关，但我认为这是值得注意的.)

Note: Your dump indicates that created_date is updated to CURRENT_TIMESTAMP if a post is modified. If this is a live message board, be aware that this might cause comments to be dated before the parent message, and it means a thread will stay on top if it is frequently modified (even with no actual change to its text). (That's not relevant to my solution, but I thought it was worth noting.)

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

Because a join is required, this query will now be much slower. My suggestion: maintain two date columns, "thread_last_modified" and "item_last_modified". You will have to cascade updates from thread-starters to comments, but I think it's worth it if there are not lots of updates, because the query can be much simpler. I haven't tested this because it requires several changes to your design:

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子句之间添加此行(对于我的第一个添加的解决方案，联接):

ADDED #2: If you want only the thread containing the comment with id ::thisOne, I think you can add this line between the ON and ORDER BY clauses (for my first added solution, the join):

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

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

In theory, this lookup should be evaluated just once for the query, but if it's not in practice, you could precompute it as ::thisOneThreadID and add

where parentfeed.id = ::thisOneThreadID

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

For the second solution, assuming you precompute again, try

where coalesce(id,reply_id) = ::thisOneThreadID

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

By the way, I suspect both of my solutions will merge threads that were last modified at the exact same time...

这篇关于复杂的SQL顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！