mysql - SQL 查询 - 按日期排序但也按组排序

Question

女士们先生们，我非常感谢您的帮助:

这是我的 table :

ID     postID    replyID    content    entry_date  1       0         40          hey         12/7  2       0         40          hi          12/8  3       0         40          whatsup     12/9  4       2         40          why?        12/10  5       0         40          who?        12/11  

I need to run a query to get it like this:

ID     postID    replyID    content    entry_date  1       0         40          hey         12/7  2       0         40          hi          12/8  4       2         40          why?        12/10  3       0         40          whatsup     12/9  5       0         40          who?        12/11  

You will see that ID 3 and 4 have switched. So basically I need to ASC by entry_date, unless ID = POSTID, then I need those two grouped together and also ASC by entry_date for those two.

Here is what I have tried but I am totally lost:

SELECT t1.ID, t1.postID, t1.replyID, t1.content, t1.entry_date
  FROM discussion t1, discussion t2
 WHERE t1.replyID = '40' AND t1.ID = t2.postID
 ORDER BY t1.entry_date ASC

基本上除了找到一行 ID = 一行 postID 之外什么都不做

Answer 1

您可以将 CASE 语句添加到您的 ORDER BY 子句中

ORDER BY 
  CASE WHEN postID = 0 THEN ID ELSE postID END
  , entry_date

这样做，您可以一起丢弃连接，整个语句可以简化为

SELECT  ID
        , postID
        , replyID
        , content
        , entry_date
FROM    discussion
ORDER BY 
       CASE WHEN postID = 0 THEN ID ELSE postID END
       , entry_date

请注意，我假设输入日期是 DATETIME 列，而不是 VARCHAR 列。