如何按时间戳排序结果顺序(其中之一除外)? [英] How to sort results order by timestamp except one of them?
本文介绍了如何按时间戳排序结果顺序(其中之一除外)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的表:
// Mytable
+----+--------------------+------+------------------+-----------+
| Id | QuestionOrAnswer | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1 | question1 | 0 | 3 | 1 |
| 2 | answer1 | 1 | NULL | 2 |
| 3 | answer2 | 1 | NULL | 3 | -- accepted answer
| 4 | answer3 | 1 | NULL | 4 |
+----+--------------------+------+------------------+-----------+
现在我想要这个结果:(请关注订单)
Now I want this result: (please focus on the order)
+----+--------------------+------+------------------+-----------+
| Id | QuestionOrAnswer | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1 | question1 | 0 | 3 | 1 |
| 3 | answer2 | 1 | NULL | 3 | -- accepted answer
| 2 | answer1 | 1 | NULL | 2 |
| 4 | answer3 | 1 | NULL | 4 |
+----+--------------------+------+------------------+-----------+
// ^ 0 means question and 1 means answer
那我该怎么做? (我想要与SO排序完全一样的东西)
Well how can I do that? (I want something exactly like SO sorting)
这是我的尝试:
SELECT * FROM Mytable WHERE 1 ORDER BY Type, {I need to add something here}, timestamp
推荐答案
您需要将表自身连接起来,以确定接受的答案是哪一行.然后可以在ORDER BY
中使用该信息:
You need to join the table to itself to determine which row is the accepted answer. Then the information can be used in the ORDER BY
:
SELECT t.*
FROM Mytable t LEFT JOIN
Mytable tans
ON t.id = tans.AcceptedAnswerId
ORDER BY t.Type,
(tans.id IS NOT NULL) DESC,
t.timestamp
这篇关于如何按时间戳排序结果顺序(其中之一除外)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文