如何按时间戳排序结果顺序(其中之一除外)?
问题描述：
我有一个这样的表:
// 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