+-
我有这样一张桌子：
// 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 |
+----+--------------------+------+------------------+-----------+
现在我想要这个结果:(请关注订单)
+----+--------------------+------+------------------+-----------+
| 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排序完全相同的东西)
这是我的尝试：
SELECT * FROM Mytable WHERE 1 ORDER BY Type, {I need to add something here}, timestamp
最佳答案
您需要将表连接到自身以确定哪一行是接受的答案.然后可以在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
点击查看更多相关文章
转载注明原文：mysql – 如何按时间戳排序结果顺序,除了其中一个？ - 乐贴网
